Styjun

I am using mysql to query a DB. What I would like to do is to query NOT the full database, but only the last 1000 rows ordered by timestamp.

I have tried to use this query which doesn't work ad I would like. I know the limit is used to return a fixed number of selected elements. But that is not what I would like. I want to query a fixed number of element.

select * from mutable where name = 'myschema' ORDER BY start_time DESC LIMIT 1000;

Any help?

tl;dr Sort Less Data

I guess your mutable table has an autoincrementing primary key called mutable.mutable_id.

You can then do this:

 SELECT mutable_id 
 FROM mutable 
 WHERE name = 'myschema' 
 ORDER BY start_time DESC 
 LIMIT 1000;

It gives you a result set of the ids of all the relevant rows. The ORDER BY ... LIMIT work then only has to sort mutable_id and start_time values, not the whole table. So it takes less space and time in the MySql server.

Then you use that query to retrieve the details:

 SELECT *
 FROM mutable
 WHERE mutable_id IN (
 SELECT mutable_id
 FROM mutable 
 WHERE name = 'myschema' 
 ORDER BY start_time DESC 
 LIMIT 1000
 )
 ORDER BY start_time DESC;

This will fetch all the data you need without needing to scan and sort the whole table.

If you create an index on name and start_time the subquery will be faster: the query can random-access the index to the appropriate name, then scan the start_time entries one by one until it finds 1000. No need to sort; the index is presorted.

CREATE INDEX x_mutable_start_time ON mutable (name, start_time);

If you're on MySQL 8 you can create a descending index and it's even faster.

CREATE INDEX x_mutable_start_time ON mutable (name, start_time DESC);

This works only with auto_increment

The trick is to sort less data, like O. Jones mentioned. Problem is in telling MySQL how to do so.

MySQL can't know what "last 1000 records" are unless it sorts them based on the query. That's exactly what you want to avoid so you need to tell MySQL how to find "last 1000 records".

This trick consists of telling MySQL at which auto_increment to start looking for the data. The problem is that you're using timestamps so I'm not sure whether this fits your particular use case.

Here's the query:

SELECT * FROM mutable 
WHERE `name` = 'myschema' 
AND id > (SELECT MAX(id) - 1000 FROM mutable WHERE `name` = 'myschema') 
ORDER BY start_time DESC LIMIT 1000;

Problems:

• auto_increments have gaps. These numbers aren't sequential, they're unique, calculated via sequential increment algorithm. To get better results, increase the subtraction number. You might get 1000 results, but you might get 500 results - depending on your dataset




• if you don't have an auto_increment, this is useless



• if the timestamps inserted are required to be sorted beforehand from larger to lower, this is useless

advantages:
- primary key is used to define the value range (where id > x), therefore dataset reduction will be the fastest possible.