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 Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png