# Slow query

**URL:** <https://forums.percona.com/t/slow-query/257>\
**Category:** Other MySQL® Questions\
**Created:** [March 23, 2007, 9:51am UTC](https://forums.percona.com/t/slow-query/257 "2007-03-23T09:51:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![eyesys](https://avatars.discourse-cdn.com/v4/letter/e/258eb7/32.png) [@eyesys](https://forums.percona.com/u/eyesys)\
**Post date:** [March 23, 2007, 9:51am UTC](https://forums.percona.com/t/slow-query/257/1 "2007-03-23T09:51:50Z")

</div>

Im running an older version of mysql, 4.0.16-nt and i get some real bad performance during a left join with is null

select distinct ih.\* from InvoiceHeader ih  
left join invoiceresponsibleuser iru on  
ih.uuid=iru.invoiceHeaderUUID  
WHERE (status = 201 AND iru.useruuid IS NULL)  
order by ih.invoiceNumber ASC

Is there an easy way to up the performance of this query? As mysql 4.0.16 doesnt handle subqueries im not sure what i can do.

/A

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 24, 2007, 5:35am UTC](https://forums.percona.com/t/slow-query/257/2 "2007-03-24T05:35:27Z")

</div>

Use EXPLAIN in front of your query to see what the database is actually doing.

How large in MB and rows are the two tables?

What is your sort\_buffer\_size and tmp\_table\_size server variables set to?  
Due to the combination of left join and order by mysql will need to create a temporary table and sort it.

Why do you have "DISTINCT ih.\* "?  
My guess is that you probably have a primary key on that table which means that the distinct is useless.  
Using DISTINCT out of habit when there is no need for it is sloppy programming and should be avoided.

---

<div class="post-metadata">

**Author:** ![eyesys](https://avatars.discourse-cdn.com/v4/letter/e/258eb7/32.png) [@eyesys](https://forums.percona.com/u/eyesys)\
**Post date:** [March 26, 2007, 1:40am UTC](https://forums.percona.com/t/slow-query/257/3 "2007-03-26T01:40:38Z")

</div>

Explain on the query gives

±------±-----±--------------±-------±--------±-------+ -------±---------------------------------------------+  
| table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±------±-----±--------------±-------±--------±-------+ -------±---------------------------------------------+  
| ih | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 12296 | Using where; Using temporary; Using filesort |  
| iru | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 2047 | Using where |  
±------±-----±--------------±-------±--------±-------+ -------±---------------------------------------------+

The size of invoiceheader is around 3.6MB and invoiceresponsibleuser is just under 300KB.

My sort\_buffer\_size is set to 2 097 144 and the tmp\_table\_size is 33 554 432.

Why distinct is used i dont really know, i was hired to clean up the Java code in a project and realized that what was really needed was to clean up some of the database queries. Database not being my field of expertise i looked to this forum for help.

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 26, 2007, 2:23am UTC](https://forums.percona.com/t/slow-query/257/4 "2007-03-26T02:23:46Z")

</div>

In your explain we can see that it doesn’t exist any indexes on either of the table.  
And as I suspected it is creating a temporary table and sorts it.

Some suggestions:  
1.  
Increase sort\_buffer\_size to about 8M that gives you a little headroom.

1. 

Add an index on the column used for the join on iru:

ALTER TABLE invoiceresponsibleuser ADD INDEX iru\_ix\_invHead(invoiceHeaderUUID);

That at least takes care of the join.

1. 

Then we can try with this.  
I’m assuming that status is part of the invoiceHeader table.  
But we can’t be entirely sure that MySQL will use this index.

ALTER TABLE InvoiceHeader ADD INDEX ih\_ix\_status(status);

Make these three changes and run the EXPLAIN again and post the result here.

---

<div class="post-metadata">

**Author:** ![eyesys](https://avatars.discourse-cdn.com/v4/letter/e/258eb7/32.png) [@eyesys](https://forums.percona.com/u/eyesys)\
**Post date:** [March 26, 2007, 2:34am UTC](https://forums.percona.com/t/slow-query/257/5 "2007-03-26T02:34:48Z")

</div>

Wow, the speed of the query just went down to 1/100th of before 😃

Explain now looks like this

±------±-----±---------------±---------------±--------+ ---------±-----±------------------------------------------ —+  
| table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±------±-----±---------------±---------------±--------+ ---------±-----±------------------------------------------ —+  
| ih | ref | ih\_ix\_status | ih\_ix\_status | 4 | const | 33 | Using where; Using temporary; Using filesort |  
| iru | ref | iru\_ix\_invHead | iru\_ix\_invHead | 251 | ih.uUID | 1 | Using where; Distinct |  
±------±-----±---------------±---------------±--------+ ---------±-----±------------------------------------------ —+

Thanks alot for your input in this i think ill look over the rest of the queries made in this application and see if i can find similar choking points.

Thanks again, really helped alot!

/A