A function on any column does not negate the use of the index. That
only happens if you use a function in a filter part (join, where, etc.).
You may want to run optimize table on the 2 tables involved. That
will update the table stats that mysql uses to optimize the queries.
MySQL may occasionally be taking a different optimization path when
it runs the query, which may or may not be the best path. Those may
be the times your query is slow. You'll notice in your explain it's
looking in the movement_items table first.
Dan Nelson was correct about MySQL having to do 2300+ lookups in the
second table, although in this case the second table is the
movements table. Which is the opposite of what you are expecting in
your query. You are correct, 2300 records should be a breeze and your
query should always be fast. You want the movements table to be
queried first.
I don't know your table structure, but it seems item_id is part of
the movement_items table. Which means you should put item_id=21311 in
the join statement.
...LEFT OUTER JOIN movements ON movements.id =
movement_items.movement_id AND movement_items.item_id=21311
From the manual:
The LEFT JOIN condition is used to decide how to retrieve rows from
table B. (In other words, any condition in the WHERE clause is not
used.)
The part in () is the important part. The WHERE clause should only
have information to filter the result set, not anything to filter the
JOIN. That information should be in the JOIN.
Try making that change. I believe I explained what is happening and
why. Although I may be wrong, please post if it doesn't work.
Regardless, you want to see the movements table listed first in your
EXPLAIN.
Brent
On Nov 9, 2007, at 3:46 PM, Moritz von Schweinitz wrote:
Thank your for your answer (even though i only got it because you
sent it directly to me - somehow, the mailinglist-emails are not
getting through to me. weird).
1.) All that can have a UNIQUE index, have one, ut the ones i am
using here are not unique (except for movements.id, which is a
PRIMARY).
2.) as far as i can see, the use of the function on the column
quant shouldnt influence performance in this case - mysql should
(and sais that it does inthe EXPLAIN, as far as i can tell), that
it is using all availble indexes to reduce the numbers of examined
rows to a mere 2300 rows, and then sums them up - which shouldn't
take longer than a second, in my experience.
3.) i think i cant combine them in an index, because type_id and
type_id are in different tables.
but my main problem is still that the first run of the query is
slow, and the following ones are fast enough - this way, i cant
really debig the query. any tips on how to manage that mysql stops
doing whatever it is doing to make the following queries optimized?
thanks,
M.
Martin Gainty wrote:
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
