Re: mysql eluding query debugging?

[Brent Baisley]

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 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? 
[EMAIL PROTECTED]








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]