can someone help me simplify this syntax

Question

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

Post the DDL for your tables (in the form of CREATE TABLE statements) and the contents of the execution plan. You've not provided any relevant details. — Ken White, Feb 15 '23 at 04:12
I'm confused - why would you join a table to itself then match on the same fields in the where clause? — P.Salmon, Feb 15 '23 at 09:17
To help you with your [tag:query-optimization] question, we need to see your table definitions, your index definitions, and the output of EXPLAIN. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. — O. Jones, Feb 15 '23 at 12:06
Are you really doing a self-join? Please explain the purpose of `count(barcode)=1`; it does not make sense if you are looking at the same table. — Rick James, Feb 16 '23 at 19:00

score 1 · Answer 1 · answered Feb 15 '23 at 05:32

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.

score 0 · Answer 2 · answered Feb 16 '23 at 18:56

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.

score -1 · Answer 3 · answered Feb 15 '23 at 03:53

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

It's not valid to use aggregate functions in a where clause. Expressions in the where clause are evaluated in the context of a single row, not groups of rows. — Bill Karwin, Feb 15 '23 at 04:38

can someone help me simplify this syntax

3 Answers3