CODEWITHSUNDEEP
mysqlindexingdatabase-performancequery-performance
Simon
Simon

Reputation: 87

Index structure for secondary keyword search

I have a table(Articles) with the following structure: 

id int(10) unsigned not_null auto_increment
title varchar(32)
system_id int(10) unsigned default 0
last_update int(10) unsigned default 0

what is the recommended index structure for the table that will provide best performance for this query : 

"SELECT * FROM Articles where system_id = {ID} order by last_update desc"

Upvotes: 0

Views: 33

Answers (2)

Tom Shir
Tom Shir

Reputation: 482

I would add both these indexes and check the EXPLAIN plan to see which of them are used. MySQL sometimes chooses the ordering index rather than the filtering one, and therefore the second one might be better in some cases.

ALTER TABLE `Articles` ADD INDEX `articles_index_1` (system_id, last_update);
ALTER TABLE `Articles` ADD INDEX `articles_index_1` (last_update);

Upvotes: 1

Rick James
Rick James

Reputation: 142298

As discussed here , start with the = column (system_id), then have the "range" column (last_update):

INDEX(system_id, last_update)

Upvotes: 1

Related Questions