SQL LIMIT to get latest records

Question

I am writing a script which will list 25 items of all 12 categories. Database structure is like:

tbl_items
---------------------------------------------
item_id | item_name | item_value | timestamp 
---------------------------------------------

tbl_categories
-----------------------------
cat_id | item_id | timestamp
-----------------------------

There are around 600,000 rows in the table tbl_items. I am using this SQL query:

SELECT e.item_id, e.item_value
  FROM tbl_items AS e
  JOIN tbl_categories AS cat WHERE e.item_id = cat.item_id AND cat.cat_id = 6001
  LIMIT 25

Using the same query in a loop for cat_id from 6000 to 6012. But I want the latest records of every category. If I use something like:

SELECT e.item_id, e.item_value
  FROM tbl_items AS e
  JOIN tbl_categories AS cat WHERE e.item_id = cat.item_id AND cat.cat_id = 6001
  ORDER BY e.timestamp
  LIMIT 25

..the query goes computing for approximately 10 minutes which is not acceptable. Can I use LIMIT more nicely to give the latest 25 records for each category?

Can anyone help me achieve this without ORDER BY? Any ideas or help will be highly appreciated.

EDIT

tbl_items

+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| item_id             | int(11)      | NO   | PRI | 0       |       |
| item_name           | longtext     | YES  |     | NULL    |       |
| item_value          | longtext     | YES  |     | NULL    |       |
| timestamp           | datetime     | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

tbl_categories

+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| cat_id         | int(11)    | NO   | PRI | 0       |       |
| item_id        | int(11)    | NO   | PRI | 0       |       |
| timestamp      | datetime   | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+

Please show your table create statements. Are your indexes correct? — Artem Goutsoul, Jan 17 '12 at 07:59
@Astha - Please can you confirm what indexes you have, and whether you can request/create new ones? — MatBailie, Jan 17 '12 at 09:45
It is not clear from the question whether the whole result should be ordered by timestamp or data within each group? — newtover, Jan 17 '12 at 10:29

score 2 · Answer 1 · answered Jan 17 '12 at 07:58

2

Can you add indices? If you add an index on the timestamp and other appropriate columns the ORDER BY won't take 10 minutes.

answered Jan 17 '12 at 07:58

Joni

108,737
14
143
193

An index is like a phone book. If there were no order in a phone book, it would take hours to look up someone's phone number. An index on the timestamp column tells MySQL where the records are located, *by timestamp* so it can look up rows like you would in a phonebook. – toon81 Jan 17 '12 at 08:06
Unfortunately, I don't know of any decent websites to get you started. (anyone?) – toon81 Jan 17 '12 at 08:06
Well, there's always the official documentation although it might not be the best introduction to the subject: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Joni Jan 17 '12 at 08:12
Yup, no indices on the timestamp column. Adding an index on the timestamp column of the items table will speed up your `ORDER BY` by a factor of thousands (and I am not exaggerating). – toon81 Jan 17 '12 at 08:14
Use a B-Tree it's a ordered index. Hash Index is not ordered. – edze Jan 17 '12 at 08:20
1

This may be daunting if you're just starting out (it was for me) but you can learn a lot. If you run a query that looks like `EXPLAIN {sql-query}` then MySQL will tell you how it would execute `{sql-query}`.http://dev.mysql.com/doc/refman/5.0/en/explain-output.html – toon81 Jan 17 '12 at 08:23
Exactly, have a column created_at, while selecting from it just `ORDER BY 'created_at' DESC LIMIT 10`. – Trishant Pahwa Jul 23 '22 at 18:06

score 1 · Accepted Answer · answered Jan 17 '12 at 08:30

First of all:

It seems to be a N:M relation between items and categories: a item may be in several categories. I say this because categories has item_id foreign key.

If is not a N:M relationship then you should consider to change design. If it is a 1:N relationship, where a category has several items, then item must constain category_id foreign key.

Working with N:M:

I have rewrite your query to make a inner join insteat a cross join:

  SELECT e.item_id, e.item_value
  FROM 
     tbl_items AS e
  JOIN 
     tbl_categories AS cat 
        on e.item_id = cat.item_id
  WHERE  
     cat.cat_id = 6001
  ORDER BY 
     e.timestamp
  LIMIT 25

To optimize performance required indexes are:

create index idx_1 on tbl_categories( cat_id, item_id)

it is not mandatory an index on items because primary key is also indexed. A index that contains timestamp don't help as mutch. To be sure can try with an index on item with item_id and timestamp to avoid access to table and take values from index:

create index idx_2 on tbl_items( item_id, timestamp)

To increase performace you can change your loop over categories by a single query:

  select T.cat_id, T.item_id, T.item_value from 
  (SELECT cat.cat_id, e.item_id, e.item_value
   FROM 
     tbl_items AS e
   JOIN 
     tbl_categories AS cat 
        on e.item_id = cat.item_id
   ORDER BY 
     e.timestamp
   LIMIT 25
  ) T
  WHERE  
     T.cat_id between 6001 and 6012
  ORDER BY
     T.cat_id, T.item_id

Please, try this querys and come back with your comments to refine it if necessary.

thanks for the reply. I cannot change the structure of the db as its not mine. but yes it is 1:N relation tables. one item_id will be only in one category. I tried this query but its also taking same amount of time. i am using a loop for the category ids a i can't use single query like this with between caluse. hope you got my point. — Astha, Jan 17 '12 at 09:41
If is not your database they are not a easy solution. Take a look to this related question: [Is cross-table indexing possible?](http://stackoverflow.com/questions/8509026/is-cross-table-indexing-possible) — dani herrera, Jan 17 '12 at 09:54

score 1 · Answer 3 · edited May 23 '17 at 10:24

Leaving aside all other factors I can tell you that the main reason why the query is so slow, is because the result involves longtext columns.

BLOB and TEXT fields in MySQL are mostly meant to store complete files, textual or binary. They are stored separately from the row data for InnoDB tables. Each time a query involes sorting (explicitly or for a group by), MySQL is sure to use disk for the sorting (because it can not be sure in advance how large any file is).

And it is probably a rule of thumb: if you need to return more than a single row of a column in a query, the type of the field is almost never should be TEXT or BLOB, use VARCHAR or VARBINARY instead.

UPD

If you can not update the table, the query will hardly be fast with the current indexes and column types. But, anyway, here is a similar question and a popular solution to your problem: How to SELECT the newest four items per category?

Thanks for this explanation. Really appreciated but as i told this database is not mine. I only have to fetch data to show. — Astha, Jan 18 '12 at 03:59

SQL LIMIT to get latest records

3 Answers3