Simple query using ORDER BY field LIMIT 0,5 reads whole 7,500 rows!

scoppc

I had a 'news' table with almost 7.500 rows:

Structure:

CREATE TABLE `news` (
  `newsId` int(10) unsigned NOT NULL auto_increment,
  `chanId` tinyint(3) unsigned NOT NULL default '0',
  `adminId` int(11) default NULL,
  `newsTitle` varchar(255) NOT NULL default '',
  `newsSubTitle` varchar(255) NOT NULL default '',
  `newsHeadline` text NOT NULL,
  `newsNews` text NOT NULL,
  `newsBy` varchar(150) NOT NULL default '',
  `newsPic` varchar(20) default NULL,
  `newsPicCaption` text,
  `newsKeywords` varchar(255) default NULL,
  `newsSource` varchar(255) NOT NULL default '',
  `newsAddedOn` int(10) unsigned NOT NULL default '0',
  `newsAddedDate` date NOT NULL,
  `newsViewed` int(10) unsigned NOT NULL default '0',
  `newsType` char(2) default NULL,
  PRIMARY KEY  (`newsId`),
  KEY `chanId` (`chanId`),
  KEY `addedon` (`newsAddedOn`),
  KEY `chanIdnewsId` (`chanId`,`newsId`)
) ENGINE=InnoDB AUTO_INCREMENT=7542 DEFAULT CHARSET=utf8;

I am doing a simple query like this:

SELECT * FROM news ORDER BY newsAddedOn DESC LIMIT 0,5

using EXPLAIN, I received this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news index NULL newsAddedOn 4 NULL 7428

how to make this simple innocent query not reading my whole table coz I only need 5 latest news.

thanks in advance.

tomhath

Put a descending index on the newsAddedOn column

http://www.sitepoint.com/print/optimizing-mysql-application/

Something like this (not tested):

CREATE INDEX news_added ON news (newsAddedOn  DESC)

scoppc

thx for the suggestion, tom.

descending index on 'newsAddedOn' does nothing.
it still reading the whole table.

I have googled and found nothing on this issue. So, I begun to wonder whether this is really an issue or not?
Reading 20 mil of data just to show latest news should be a joke.

I am now considering creating a table 'newslatest' to store the ID of the 5 latest news.

HalfaBee

use LIMIT 1,5 not 0,5

tomhath

Are you sure it's reading 20MB of data? It says 7500 rows, with all the varchar columns it's probably nowhere near 20MB. If that's the case MySQL is probably just reading the entire table into memory and doing a table scan. Most DBMS' will do that for small tables (I know SQL Server will always read a table that's less tha 8 M😎.

scoppc

no, tom.
I'm actually talking about 20 mil rows of data (my database will eventually reach there soon, now it's only 7,500 rows)
and I think it will be a disaster to read 20 mil rows of data when I only need 5. 🙂

tomhath

You could try forcing it to use the index and see what happens

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;

leatherback

Hi,

Adding an index is the way to solve this issue. Basically, in order to find the last records the database needs to sort them first. Adding an index on the newsAddedOn will only work if there are a serious number of records with the same value for newsAddedOn which is unlikely. However, unless you update the news items, you could just sort by the record ID, which should reduce the number of records hit for the query if this is a unique key / autoincrement type.

tomhath

leatherback;10895888 wrote:
Adding an index on the newsAddedOn will only work if there are a serious number of records with the same value for newsAddedOn which is unlikely.

Not sure I follow what you're saying. His query has an ORDER BY newsAddedOn, how would an index on that column not help find the three most recent rows?

leatherback

An index does exactly what it sais: It creates an index of the values in that colum. If a lot of record ahve the same content, the index will be much shorter than the actual record listing, like the index of a book. The index just has a list of values ,with references to the records that contain that value. Hence, if a field contains almost only unique values (Like the moment at which a record was added) the index will be pretty much as big as the actual list of values.

tomhath

leatherback;10895916 wrote:
if a field contains almost only unique values (Like the moment at which a record was added) the index will be pretty much as big as the actual list of values.

The index will contain as many entries as the number of values, true. But the goal is to find specific values quickly. The index is ordered by the indexed columns, contains only the indexed columns (so it's much smaller than the table itself), and is stored in a structure optimized for searching by value. An index on a column with a lot of duplicate values is less helpful, since it doesn't reduce the number of rows fetched as much.

In this case, by indexing the search criteria (the column that's sorted and limited) the index should be able to find the desired rows very quickly.