Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Slow query
Slow query [message #977] Fri, 23 March 2007 11:21 Go to next message
eyesys is currently offline eyesys
Messages: 3
Registered: March 2007
Junior Member
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
Report message to a moderator
Re: Slow query [message #979 is a reply to message #977 ] Sat, 24 March 2007 07:05 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
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.
Report message to a moderator
Re: Slow query [message #984 is a reply to message #977 ] Mon, 26 March 2007 03:10 Go to previous messageGo to next message
eyesys is currently offline eyesys
Messages: 3
Registered: March 2007
Junior Member
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.
Report message to a moderator
Re: Slow query [message #986 is a reply to message #977 ] Mon, 26 March 2007 03:53 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
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.

2.
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.

3.
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.
Report message to a moderator
Re: Slow query [message #987 is a reply to message #977 ] Mon, 26 March 2007 04:04 Go to previous message
eyesys is currently offline eyesys
Messages: 3
Registered: March 2007
Junior Member
Wow, the speed of the query just went down to 1/100th of before Very Happy

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
Report message to a moderator
Previous Topic:From time to time very slow UPDATE
Next Topic:ORDER BY slowing down query
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Mon Jul 6 17:15:54 EDT 2009

Total time taken to generate the page: 0.01749 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.