Best way to get the latest 5 posts from a category?

Question

I have setup my news script so it will display the latest 5 posts (from the same category as the currently viewed post - excluding the currently viewed post).

My SQL looks like this :

SELECT title, sid, url, category
FROM news 
WHERE category = ? AND sid <> ? ORDER BY sid DESC LIMIT 5

Here is the EXPLAIN for the query :

+----+-------------+----------+------+------------------+----------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys    | key      | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | news     | ref  | PRIMARY,category | category | 98      | const |  154 | Using where |
+----+-------------+----------+------+------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

What I'm wondering is - is there any way to optimize my query so it doesn't have to scan so many rows to get the 5 results?

EDIT

Results with Category Index :

mysql> EXPLAIN EXTENDED SELECT title, sid, url, category FROM news WHERE category = ? AND sid <> ? ORDER BY sid DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: news
         type: ref
possible_keys: PRIMARY,category
          key: category
      key_len: 98
          ref: const
         rows: 156
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Results without Category Index :

mysql> EXPLAIN EXTENDED SELECT title, sid, url, category FROM news WHERE category = ? AND sid <> ? ORDER BY sid DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: news
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 5
     filtered: 7420.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Daniel Gimenez · Answer 1 · 2013-09-22T14:50:14.180

3

Edit: This answer does not work

Based on @Mayhem's comment below, specifying a column index in descending order does nothing, though may do something in the future. This is even true in version 5.7. I think this is useful to keep this up because people might think using DESC actually does something.

Original Answer

If you create an index on sid in descending order (order is important), then that should be used in the ORDER BY clause and improve the results by a lot.

The create statement should look something like this:

CREATE INDEX IX_news_category_sid
    ON news (category, sid DESC)

edited Sep 22 '13 at 14:50

answered Sep 20 '13 at 06:11

Daniel Gimenez

18,530
3
50
70

How do I use that index in the 'order by' clause? – Brian Smith Sep 20 '13 at 06:22
The Query Optimizer will choose to use the index by itself, you don't have to specify anything. – Daniel Gimenez Sep 20 '13 at 06:28
The query optimizer still chooses the 'category' index over anything else. When I do a 'FORCE INDEX' (as a test), it now needs to scan over 300 rows using the new index. – Brian Smith Sep 20 '13 at 06:43
I'm still having the same issue :/ (really appreciate the assistance though) - it still wants to use category index and when I use "FORCE INDEX", it needs to scan 150 rows. – Brian Smith Sep 20 '13 at 07:00
Try this: `RESET QUERY CACHE;` run with the index, then do the reset again and remove the index and run again. Whichever runs better, keep -- though the index might do better in long run. – Daniel Gimenez Sep 20 '13 at 07:06
1

I figured out why it wasn't working : "An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order" – Brian Smith Sep 22 '13 at 00:44
@Mayhem, according to some other SO answers, MySQL search the index in either direction, so this answer may actually work. I think you might try having ***two indexes***, the one above, and one on just `sid`. That might have the best result. – Daniel Gimenez Sep 23 '13 at 13:37
'sid' has always been my primary index - still have the same results. There is no other solution to this problem? – Brian Smith Sep 25 '13 at 02:38
Can you add the output to show warnings in your quesion? Can show the output with an without the index, after reset query cache? http://dev.mysql.com/doc/refman/5.7/en/explain-extended.html – Daniel Gimenez Sep 25 '13 at 12:24
wow .. removing the category index solved all my issues. See edit in original question. – Brian Smith Sep 27 '13 at 01:05
That sounds wrong. I don't know exactly what filtered means, but to compare the two results I think you need to multiple rows by filtered. – Daniel Gimenez Sep 27 '13 at 01:14
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. This column is displayed if you use EXPLAIN EXTENDED. – Brian Smith Sep 27 '13 at 01:19
According to http://stackoverflow.com/questions/4140107 - it would appear this is good news? – Brian Smith Sep 27 '13 at 01:24
Lol. I really want to figure this out, because you don't have previous tables to join to. What is your primary key, and what indexes do you have with the second EXPLAIN? – Daniel Gimenez Sep 27 '13 at 12:29
My primary key is 'sid' - I do not have any other indexes with the second explain. – Brian Smith Sep 27 '13 at 21:13

score 0 · Answer 2 · answered Sep 20 '13 at 06:13

You can create another table for the latest ones. This comes pretty handy when you have more than 10 million rows. Simply create a trigger for the actual table for insert and delete so you will always have update records and only how much rows you want.

score 0 · Answer 3 · answered Sep 20 '13 at 06:18

please refer to this website for Table Indexing this improves your search a lot.

The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table.

link: http://www.w3schools.com/sql/sql_create_index.asp

Best way to get the latest 5 posts from a category?

3 Answers3