Adding indexes to table

Question

I have a datatable that gets the data from server side, and I want to optimize my table by adding indexes so that the sorting is faster (right now it takes around 7 seconds to sort 60K rows)

My 'copy create statement to clipboard' looks like this:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` char(128) COLLATE utf8_unicode_ci NOT NULL,
  `salt` char(5) COLLATE utf8_unicode_ci NOT NULL,
  `joined` int(10) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `surname` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `role` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `photo` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_type` int(11) DEFAULT NULL,
  `user_owner` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=81634 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The problem that I'm having is that if I use this:

ALTER TABLE `users` ADD INDEX (`email`, `name`, `surname`, `user_type`) ;

only email is indexed (at least only the email column can be sorted fast). I'm not sure what I can do to add the other indexed columns (or if it's actually going to be good for optimizing it).

Ariel · Accepted Answer · 2012-08-24 01:40:59Z

1

You have to add a separate index for each column you want to use.

But be aware: MySQL can only use an index for the where clause, or the order by. But not both at once. Unless, it is the same index for both. But in that case the where clause must be a constant (i.e. it must match a single value).

See also: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

answered Aug 24, 2012 at 1:40

Ariel

26.8k5 gold badges62 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Ariel Over a year ago

@luqita Post an explain plan for both ways.

Ariel Over a year ago

@luqita If the explain shows the same thing both ways then it will run at the same speed both ways. Do you know how to generate an explain?

Ariel Over a year ago

@luqita No, explain select, not explain table. It gives you a table with info about how it's going to process the query. It's invaluable when trying to find the fastest way to do a query.

Ariel Over a year ago

@luqita No, the change in rows from 81729 to 81681 is meaningless. You have a full table scan plus filesort - i.e. the worst possible result. The reason the query may appear to be faster sometimes is that it gets cached. The change you made to the index is having no effect. It doesn't make sense though - it should be using the index for at least the sorting.

Ariel Over a year ago

@luqita I found a bug report with this issue: bugs.mysql.com/bug.php?id=54225 looks like you need to force the index. SELECT * FROM users FORCE INDEX(email) ORDER by email The same things happens to be BTW - I was unable to get MySQL to use the index for sorting in any test, except by forcing it.

|

Collectives™ on Stack Overflow

Adding indexes to table

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related