MySQL performance issue, using LIMIT

[bruceg]

I seem to be having an issue with MySQL 3.23.47. I have a table, that stores a lot of BLOB data (images), and now that the table is getting larger, because of the graphics being stored, when I use a LIMIT in my query, it gets slow. If I SELECT one record, everything is fast, but if I issue sometime like this (to get the last 30 records added):

select * from images limit 1081,30

The query can range from 8-15 seconds.

The current row size is: 100MB, and will continue to grow t get quite large over time. Currently there is 1111 records in the table.

TIA-

Bruce

[Anon]

Bruce Garlock wrote:

select * from images limit 1081,30

Is this your real query? LIMIT without an ORDER BY clause is akin to running a server on windows - it should be avoided at all costs. I suppose you are doing ALTER TABLE .... ORDER BY, or somehow you know the clustering of your data.

For performance, limit isn't a good choice for selecting records as you won't use an index. Check EXPLAIN to see. You may want to rethink your design, and have a timestamp for an effective WHERE clause. I don't store images in a database anyway, but you may be constrained to do this for reasons unknown to me.

[Anon]

Actually, I was taking this SELECT from phpMyAdmin, where I jumped to the last 30 records added.

Thank\'s for the insight - I didn\'t realize how much the ORDER BY would help. Now that I use that, things are much snappier.