On Fri, 15 Dec 2006 19:25:22 +0000, Brian Wakem <no@email.com> wrote:
> Ignoramus24559 wrote:
>
>> I am in big trouble with MySQL 5.
>>
>> I have a huge table, about 25 GIGABYTES, that is a cache for hard to
>> compute items. Each row has two timestamps, last updated and last
>> retrieved timestamp, some metadata, key and a BLOB.
>>
>> I would like to clean that table based on timestamps. I used to clean
>> it periodically, but doing a big DELETE...WHERE query locks it up for
>> HOURS and that means downtime for my website, lost money, pissed off
>> tutors, etc.
>>
>> So, I thought, I would write a process that would do
>>
>> SELECT id FROM bincache WHERE ... LIMIT 100
>>
>> and that would run quickly to give me 100 IDs, that would then DELETE
>> at my leisure without locking up the table. Repeat ad infinitum.
>>
>> Well, I just learned that LIMIT 100 is applied after ALL select work
>> is done, which means that it would have to search gigabytes of data
>> and return a many gigabyte result, only to trim it to 100 rows at the
>> last moment.
>>
>> Even SELECT..LIMIT 1 takes forever and I had to kill it.
>>
>> How can I get around this awful problem.
>
>
> If you ORDER BY something that is indexed then it will read the table in the
> order of the index and stop after it has match 100 rows, it should be very
> fast.
>
>

Brian, this is beautiful and seems to work. At least when I am making
my selects without having a lot of entries cleaned in the beginning of
the index. I am a little concerned that this select...order by would
become slower if it goes through the index, first scrolling through
thousands of entries that already are devoid of deletable items.

Anyway, I will see what I can do...

i