3

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.

Joelio
  • 4,621
  • 6
  • 44
  • 80

1 Answers1

1

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
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • 1
    that fixes it, thanks, I guess im passing an int to rails and thats why its doing it this way, I need to research a bit, but this seems to be the problem. I may change it to a bigint – Joelio Mar 24 '11 at 20:03
  • If all values are bigints, you'll be much better off changing the column type to bigint as opposed to varchar. – Ike Walker Mar 24 '11 at 20:04