Slow query

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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.

4

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.

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.

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.

5

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

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