List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:November 10 2007 3:11pm
Subject:Re: mysql eluding query debugging?
View as plain text  
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" <moritz@stripped>
>> To: <mysql@stripped>
>> 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? 
>>> unsub=mgainty@stripped
>>>
>>>
>>>
>>
>>
>

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