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
What I want to know are:
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.