I have a query that is performing poorly. Server version: 5.1.37-1ubuntu5.1 (Ubuntu)
SELECT * FROM `influencers` WHERE (`influencers`.`twitter_id` = 86861293) LIMIT 1
show create table influencers
influencers CREATE TABLE `influencers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`twitter_id` varchar(255) DEFAULT NULL,
`display_name` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`screen_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_influencers_on_twitter_id` (`twitter_id`),
KEY `influencers_screen_name` (`screen_name`)
) ENGINE=InnoDB AUTO_INCREMENT=504126 DEFAULT CHARSET=latin1
explain SELECT * FROM `influencers` WHERE (`influencers`.`twitter_id` = 86861293) LIMIT 1
id select_type table type possible_keys key key_len ref rows Extra
-- ----------- ----------- ---- ------------------------------- ------ ------- ------ ------ -----------
1 SIMPLE influencers ALL index_influencers_on_twitter_id (null) (null) (null) 553716 Using where
The table has 547545 rows in it.
As you can see the explain has the possible key, but not showing it using the actual key.
Any ideas? Seems like this should work and im doing something dumb.
Since twitter_id is a VARCHAR, you should put the value in quotes in your WHERE clause to avoid any implicit type conversion that may prevent MySQL from using the optimal execution plan:
SELECT *
FROM influencers
WHERE influencers.twitter_id = '86861293'
LIMIT 1