CODEWITHSUNDEEP
mysqlperformanceselectindexing
chattsm
chattsm

Reputation: 4739

Will this MySQL index increase performance?

I have the following MySQL table:

CREATE TABLE `my_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tstamp` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I want to optimise for the following SELECT query only:

SELECT `id`, `name` FROM `my_data` ORDER BY `name` ASC

Will adding the following index increase performance, regardless of the size of the table? 

CREATE INDEX `idx_name_id` ON `my_data` (`name`,`id`);

An EXPLAIN query suggests it would, but I have no quick way of testing with a large data set.

Upvotes: 0

Views: 999

Answers (2)

JoDev
JoDev

Reputation: 6873

Index are usefull when you use the column in the where clause, or when the column is a part of the condition for a link between two tables. See MySQL Doc

Upvotes: 1

Mike Dinescu
Mike Dinescu

Reputation: 55760

Yes it would. Even though you are still doing a full table scan, having the index will make the sort operation (due to the order by) unnecessary.

But it will also add overhead to inserts and delete statements!

Upvotes: 1

Related Questions