Question 20812 in Mysql Question posted in
The official MySQL documentation can be found here.

can someone help me simplify this syntax – Mysql

can someone help me make it simple and fast?

select k1.sto, k1.mm,k1.dd, k1.yy, k1.sto_name, k1.trannum,
  count(k2.barcode) 
from trans012020 as k1 , trans012020 as k2 
where k1.barcode=123456789 
  and k1.mm=k2.mm
  and k1.dd=k2.dd
  and k1.yy=k2.yy
  and k1.sto=k2.sto
  and k1.trannum=k2.trannum 
group by k1.trannum
having count(k2.barcode)=1;

if I run it, it should display all the details I need where count(barcode)=1.

it displays what I need but it took 6mins to display 5 rows of data.

also it took 6mins to display me an empty data

Tags:

3

Answers


  1. 0

    So, here are my thoughts, didn’t do a POC, but from documentation

    The select is evaluating everything until the group by, this mean is grouping everything in your database regardless of the having condition.

    my suggestion is for you to try to do it like

    select k1.sto,k1.mm,k1.dd,k1.yy,k1.sto_name,k1.trannum,count(k2.barcode) from trans012020 as k1 , trans012020 as k2 
where 
count(k2.barcode)=1 and
k1.barcode=123456789 and k1.mm=k2.mm and k1.dd=k2.dd and k1.yy=k2.yy and k1.sto=k2.sto and k1.trannum=k2.trannum 
group by k1.trannum;

    Hopefully that gets you the desire result

    Here is some documentation on how these are evaluated

    https://www.mysqltutorial.org/mysql-having.aspx

    Login or Signup to reply.
  2. 0

    Using is a convient way to join where the column names are identical. As using and on are incompatible options I’ve put the barcode criteria in where and because its an inner join this has the same effect as a join criteria.

    MySQL allows a count alias like k2count to be used later. So a partial simplification is:

    select k1.sto, k1.mm,k1.dd, k1.yy, k1.sto_name, k1.trannum,
  count(k2.barcode) as k2count
from trans012020 as k1 
join trans012020 as k2 
  using (mm,dd,yy,sto,trannum)
where k1.barcode=123456789 
group by k1.trannum
having k2count=1;

    Some more work here is required as if you are grouping by trannum, assuming that isn’t the primary key, which k1.{sto,mm,dd,yy,sto_name} fields do you expect it to display. (see Don’t disable only_full_group_by.

    Correct indexing will help with the query. See Rick’s ROT, indexing, or add the show create table trans012020 into your question.

    Login or Signup to reply.
  3. 0

    Keep in mind how JOIN and GROUP BY work together. First the JOINs are done. This explodes into a big temp table. Second the GROUP BY shrinks id down to essentially what you started with. Let’s avoid that "inflate-deflate":

    select  k1.sto, k1.mm,k1.dd, k1.yy, k1.sto_name, k1.trannum,
        ( SELECT count(k2.barcode) FROM trans012020 as k2
              WHERE k1.mm=k2.mm
                and k1.dd=k2.dd
                and k1.yy=k2.yy
                and k1.sto=k2.sto
                and k1.trannum=k2.trannum 
        ) as k2count
    from  trans012020 as k1
    where  k1.barcode=123456789
    having  k2count=1;

    If barcode is NOT NULL, change count(k2.barcode) to simply COUNT(*).

    k1 needs an INDEX (or the PRIMARY KEY) beginning with barcode.

    If k2count can never be more than 1, then there is an even better way:

    select  k1.sto, k1.mm,k1.dd, k1.yy, k1.sto_name, k1.trannum
    from  trans012020 as k1
    where  k1.barcode=123456789
      AND EXISTS ( SELECT 1 FROM FROM trans012020 as k2
              WHERE k1.mm=k2.mm
                and k1.dd=k2.dd
                and k1.yy=k2.yy
                and k1.sto=k2.sto
                and k1.trannum=k2.trannum )

    And, yes, this is desirable for k2:

    INDEX(mm, dd, yy, sto, trannum)

    (The order of the columns is not important for this query.)

    Note that the GROUP BY went away.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search