MySQL Forums :: General :: Query optimization question


Advanced Search

Query optimization question
Posted by: Nino Skilj ()
Date: February 27, 2009 04:34PM

Hi! I've been working on optimizing our database and have set up a few logs and optimized several queries. I turned on slow_logs and have just two that take a really long time. One really has me stumped... Any ideas?

I ran the following on my slow query (that takes up to 15 seconds):
-> explain SELECT SUM(Hours) AS totalHours
-> FROM hourstable
-> JOIN userstable ON userstable.id = hourstable.userid
-> WHERE userstable.companyid = '1234'
-> AND hourstable.paid = 0
-> AND userstable.status = 1
-> AND hourstable.timer = 1;

The explain result shows the following:
+----+-------------+---------+--------+----------------------------------------+---------------+---------+--------------------------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+----------------------------------------+---------------+---------+--------------------------------+------+-----------------------------------+
| 1 | SIMPLE | hourstable | ref | ix_totalsum,ix_userid | ix_totalsum | 1 | const | 10 | Using where with pushed condition |
| 1 | SIMPLE | userstable | eq_ref | PRIMARY,ix_companystatus | PRIMARY | 4 | timeclock_alpha.hourstable.userid | 1 | Using where |
+----+-------------+---------+--------+----------------------------------------+---------------+---------+--------------------------------+------+-----------------------------------+


My question is... if it's only looking at 11 rows, why is it taking 15 seconds??? Am I missing something? Reading this information wrong? There are about 1.5 million records in the hourstable. Is that really making an impact on the query? I'm assuming I'm doing something wrong since I keep reading about people constantly handling 10+ million rows of data...

Any advice would be appreciated. Thanks in advance.

Nino

Options: ReplyQuote


Subject Written By Posted
Query optimization question Nino Skilj 02/27/2009 04:34PM
Re: Query optimization question Arjen Lentz 02/28/2009 06:36AM
Re: Query optimization question Rick James 02/28/2009 04:03PM
Re: Query optimization question Nino Skilj 03/02/2009 01:41PM
Re: Query optimization question Rick James 03/02/2009 09:31PM
Re: Query optimization question Nino Skilj 03/02/2009 01:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.