[Resolved] Help needed with slow query

tunage

Hello All

I have a query which returns all clients in my database and the number of orders they have placed. Client details are stored in table "client" and orders they have placed are stored in "client_order".

"client" contains 725 records
"client_order" contains 930 records

Database in MySQL v4.1.11

This is my query -

select client.*, count(client_order.clientID) as num_orders 
from client left join client_order 
on client.clientID = client_order.clientID 
group by client.clientID 

-------------
725 rows in set (0.98 sec)
-------------

Any idea why this is running so slowly? Nearly a second seems to be ridiculously slow and all other queries i use execute in approx 0.01 - 0.06 seconds. If i limit the number of results returned it doesnt make any difference either.

The below query just queries the client_order table and returns the number of orders each client has ordered - it executes in 0.01 seconds.


select client_order.clientID, count(client_order.clientID) as num_orders 
FROM client_order 
group by client_order.clientID 
order by clientID

-------------
698 rows in set (0.01 sec)
-------------

It appears to be the join thats slowing it down, is there a more efficient way of writing the query?

Thanks in advance

dhodge

both the clienIDs are indexed right?

Sxooter

Why are you grouping them? That could be resulting in a rather large internal and uneeded sort/uniq step as well.

tunage

Nope, i want using indexes, one of those thing i'd heard about but (until now) never needed to use due to the relatively small amount of data id used.

Just indexed clientID on both tables and execution is down to 0.01 sec.

Big thanks to dhodge!

😃 😃

I found this website very usefull for explaining how/why/when to use indexes.

tunage

Sxooter, I am using count(client_order.clientID) - clients can place many orders so i need to group the results by clientID. All is ok now, thanks anyway.

Sxooter

Oh, no prob. Just some folks use group by and don't realize the costs that can pop up with certain datasets/

Note that for portability's sake you technically need a max(field) for every field you're getting back, since otherwise you could be getting any field, and most databases will choke with such a query, including certain configurations of MySQL...

No biggie, but it's just something to be aware of in the future...

csn

For counts I just add a count field and create a trigger to update it after every insert/update/delete. If you have a lot of records, aggregates can be slow and bog things down.

Sxooter

csn: True. While the problem is especially bad for PostgreSQL since it can't use an index (alone) for count(*), it's still a problem for other databases as well.

I wonder if someone's already written such a trigger for MySQL 5 now that it has triggers.

I have 5.0.4 installed on my machine, but haven't had a chance to really play with any of the advanced features just yet... hmmm.

csn

Originally posted by Sxooter
csn: True. While the problem is especially bad for PostgreSQL since it can't use an index (alone) for count()

Why can't PG? I think I remember reading in mysql's docs that there's a special value in indices or maybe the table's meta data that allows count() to do a simple lookup of a value that is the total number of rows.

Sxooter

PostgreSQL's in store MVCC means that indexes just tell you where the values might be.

You still have to look at the actual current version of a tuple in the table to see if the value is visible.