Results 1 to 21 of 21
Thread: MySQL Indexing a 'Status' field
Nov 21, 2013, 00:07 #1
MySQL Indexing a 'Status' field
Following is my table DDL
CREATE TABLE `users` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `fullname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `status` int(1) NOT NULL, `createtimestamp` int(11) NOT NULL, PRIMARY KEY (`userid`), ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
and my query to get a list of active users by their creation date is the following
SELECT userid, fullname FROM users WHERE status='1' ORDER BY createtimestamp ASC
1) What type of indexes do I have to set for the "createtimestamp" field since its used in the query to sort records.
2) Since the "status" field is INT datatype and holds just 1 and 0, does this field also needs to be indexed? If yes, which index?
3) Will it do any good if I set the "status" field as ENUM and have "ACTIVE" / "INACTIVE" instead of the current INT with 1 and 0 values? If yes why?
Thanks for any inputs.