List:General Discussion« Previous MessageNext Message »
From:Moritz von Schweinitz Date:November 9 2007 6:16pm
Subject:mysql eluding query debugging?
View as plain text  
Ok, i don't get it.

I have the following query:
SELECT
SUM(quant)
FROM
movement_items
LEFT OUTER JOIN movements ON movements.id = movement_items.movement_id
WHERE
item_id = 21311
AND
movements.type_id = 1

where 'movement_items' has about 1.3M rows, and 'movements' about 0.5M rows.

EXPLAIN gives me the following:
+----+-------------+----------------+--------+---------------------+---------+---------+-----------------------------------+------+-------------+
| id | select_type | table          | type   | possible_keys       | 
key     | key_len | ref                               | rows | Extra       |
+----+-------------+----------------+--------+---------------------+---------+---------+-----------------------------------+------+-------------+
|  1 | SIMPLE      | movement_items | ref    | movement_id,item_id | 
item_id | 5       | const                             | 2327 | Using where |
|  1 | SIMPLE      | movements      | eq_ref | PRIMARY,type_id     | 
PRIMARY | 4       | pague9.movement_items.movement_id |    1 | Using where |
+----+-------------+----------------+--------+---------------------+---------+---------+-----------------------------------+------+-------------+
2 rows in set (0.01 sec)

which seems ok to me (2327 rows to examine should be a breeze, right?)

Now, my problem: sometimes, this query takes up to 10 seconds to 
complete. So I'm trying to optimize the hell out of it - but, the 
(usually) first time i run this query, it's slow, but the subsequent 
times it's fast enough (aprox. 0.1 secs), which isn't exactly helpful 
for optimizing. So i tried to FLUSH QUERY CACHE, but it's still 'too 
fast'. Even when after the table gets updated, it's still fast. But, 
after a couple of minutes, out of the blue, the query crawls again, for 
no apparent reason i can find.

Thus, my questions:
- what's wrong with that query? I know they are big tables, but 
according to EXPLAIN, this should be fast enough, because mysql's seeing 
the indexes just fine.

- how can i tell mysql to switch off whatever cache or performance thing 
it is that makes debugging such a PITA? is there a way to disable it 
just for this query? (SELECT SQL_NO_CACHE doesn't seem to make a difference)

thanks,

M.
Thread
mysql eluding query debugging?Moritz von Schweinitz9 Nov
  • Re: mysql eluding query debugging?Dan Nelson9 Nov
  • Re: mysql eluding query debugging?Jeremy Cole11 Nov
Re: mysql eluding query debugging?Moritz von Schweinitz9 Nov
  • Re: mysql eluding query debugging?Brent Baisley10 Nov