CODEWITHSUNDEEP
mysql
Elect2
Elect2

Reputation: 1583

Performance of selecting articles from multiple categories

Table

CREATE TABLE IF NOT EXISTS `articles` ( `id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `title` varchar(100) NOT NULL, `created` int(11) NOT NULL, `updated` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; ALTER TABLE `articles` ADD PRIMARY KEY (`id`), ADD KEY `category_id` (`category_id`,`created`);

My testing:

query1

SELECT sql_no_cache * FROM `articles` WHERE category_id=1 order by created limit 0,15
Average time: 0.0003

Explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles ref category_id category_id 4 const 1029 Using where

query2

SELECT sql_no_cache * FROM `articles` WHERE category_id=1 order by updated limit 0,15
Average time: 0.0019

explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles ref category_id category_id 4 const 1029 Using where; Using filesort

query3

SELECT sql_no_cache * FROM `articles` WHERE category_id in (1,2,3) order by created limit 0,15
Average time: 0.0018

explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles range category_id category_id 4 NULL 1105 Using where; Using filesort

query4

SELECT sql_no_cache * FROM `articles` WHERE category_id in (1,2,3) order by updated limit 0,15
Average time: 0.0018

explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles range category_id category_id 4 NULL 1105 Using where; Using filesort

My final need is query3.

But from the testing I think only query1 is using index, because the execute time is much lower than the others.

Question:

  1. There is index on created, no index on updated, why query3 and query4 got save execute time?
  2. How can I make the query3 to use index, or how can improve performance of the Query3?

Upvotes: 0

Views: 56

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29639

According to this MySQL document, MySQL cannot use an index for sorting if "The query uses ORDER BY on nonconsecutive parts of an index". And that would be the case with an "in" clause.

If your ID column is an auto-incrementing integer, and created refers to the date the record was created, you may be able to order by ID instead. This will achieve what I believe you're trying to do, but it is, of course, a bit of a hack...

Upvotes: 1

Bhupesh
Bhupesh

Reputation: 881

are you use "created" is a table keyword you don't use as a field name. but
select title from articles where category_id in(1,3,7,12,234,333,456) order by created DESC limit 0,20 or select art.title from articles as art where art.category_id in(1,3,7,12,234,333,456) order by art.created DESC limit 0,20

try this is work

Upvotes: 0

Related Questions