Hi!
>>>>> "Sasha" == Sasha Pachev <[EMAIL PROTECTED]> writes:
<cut>
>> 2) select bigblob where optimized_index_clause order by
>> optimized_index_clause limit 6650,50
>>
>> Problem: I used to think that since the start number is high in the limit,
>> that it was slow because the data was not often used and therefore not
>> cached (the second try at the query is fast). I think that is part right --
>> I think the problem is that MySQL starts using the data file (with the big
>> blob) before it needs to. Ideally (and maybe MySQL does this) it should use
>> the index file only until the where _and_ limit clauses are satisfied, then
>> go get data from the data file. My gut tells be that MySQL is causing lots
>> of the blob data to be read from disk even though it is not needed. I have
>> the EXPLAIN below.
Sasha> I think you are right. The first workaround that comes to my head is to
Sasha> select all the matching values of the primary key into temp table, and then
Sasha> just perform a join. Monty may have some additional comments.
Yes, Steven is right here
Using a temporary table is also a workaround for this.
To do a general solution for this is however not trivial for the
general case. For example, if the 'optimized_index_clause' contains
any column that is not part of the index, MySQL would have to retrieve
the row (and the blob) from the data file anyway.
In principle it would be possible to run with 'index only' until we
have found 6550 rows from the table, but this would only work when you
are not joining with another table with any join type other than 'eq_ref'
(as in this case we have a hard time knowing when to switch on reading
of rows).
<cut>
Regards,
Monty
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
