In the last episode (Nov 09), Moritz von Schweinitz said:
> 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.
10 seconds is perfectly reasonable considering mysql has to do 2327
random-access lookups into your 2nd table to fetch those records. This
doesn't even include the 2327 index fetches on your primary key just to
figure out where the record is :)
Assuming a 7200rpm disk (120 IOPS) and empty caches, you could expect
the worst case query to take 4654/120 ~= 40 seconds. Chances are your
primary key index is going to be in memory most of the time, though,
and if your records are clustered together you may not have to do a
disk seek for every record.
> 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.
Nothing's wrong with the query; it's performing as expected given your
indexes. Now you can speed up the uncached query time by creating
another index on (movement_id,type_id) on your movements table (or
extending your primary key to cover type_id). That will let mysql get
all the info it needs from an index without doing the table lookup.
The drawback is you now have another index to update and cache, so
you'll take a little longer to do inserts and consume a bit more of
your key buffer.
> - 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)
You're probably running into your OSes file cache. Depending on the OS
you're running, you might be able to do a directio mount, which will
bypass the OS cache for the entire filesystem. You probably don't want
to do this except during testing.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
