mysql eluding query debugging? 2007-11-09 - By Moritz von Schweinitz
Back
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
