some unknowns
1)Are all the columns you are referencing indexed with UNIQUE indexes?
2)Using a function on any column negates the use of the referencing index so
in your case you are doing a SUM(quant)
.is there any capability of denormalising say ..storing the sum preferably
in the movement items table
3)assuming either
Both item_id and type_id columns can be referenced via UNIQUE indexes
OR using a concatened index on item_id and type_id
will go a long way to speed up the query
Viel Gluck/
Martin
----- Original Message -----
From: "Moritz von Schweinitz" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, November 09, 2007 1:16 PM
Subject: mysql eluding query debugging?
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/[EMAIL PROTECTED]
