CODEWITHSUNDEEP
phpmysql
Brian Smith
Brian Smith

Reputation: 1481

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

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)

Upvotes: 2

Views: 98

Answers (3)

Daniel Gimenez
Daniel Gimenez

Reputation: 20589

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)

Upvotes: 3

Mani Deep
Mani Deep

Reputation: 1356

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

Upvotes: 0

Kuzgun
Kuzgun

Reputation: 4737

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.

Upvotes: 0

Related Questions