List:General Discussion« Previous MessageNext Message »
From:Moritz von Schweinitz Date:November 9 2007 8:46pm
Subject:Re: mysql eluding query debugging?
View as plain text  
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=1
>>
>>
>>     
>
>   


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