I have a long, but simple, query that seems to take an incredibly long time to return a result (over 2 seconds).
Here is the query:
WHERE (`title` = 'Surprise Kanye West performance ends in fans\' disappointment'
AND `content` = '<p>It was an only-in-New-York moment: the announcement of a surprise Kanye West performance that drew throngs of people to the streets of Manhattan in the middle of the night. But instead of a concert to remember, the night ended with a hoard of disappointed fans and allegations that police used pepper spray to disperse them.<br/>Popular: <a href=\"http://podcast.cnn.com/anderson-cooper-360/episode/all/065F3vnWEzaATm/ac360-special-2016-01-07.html\" rel=\"noreferrer\" target=\"_blank\">Guns in America</a> | <a href=\"http://podcast.cnn.com/anderson-cooper-360/episode/all/09mJDnGHBvEtl7/6cgs1a.1-1.html\" rel=\"noreferrer\" target=\"_blank\">Sanders Demands Clinton Apologize</a> | <a href=\"http://podcast.cnn.com/fareed-zakaria-gps/episode/all/3m0KewVpkReuAh/gfaw6g.1-1.html\" rel=\"noreferrer\" target=\"_blank\">Blindsided: How ISIS Shook The World</a><br/></p>'
AND `poster` = '')
OR (`title` = 'Surprise Kanye West performance ends in fans\' disappointment'
AND `url` = 'http://www.cnn.com/2016/06/06/entertainment/kanye-west-surprise-concert-canceled/index.html'
AND `poster` = '')
OR `url` = 'http://www.cnn.com/2016/06/06/entertainment/kanye-west-surprise-concert-canceled/index.html' LIMIT 1;
If you put a compound index on
(title, content, poster), and another index on
url, you should see a speedup.
Note well. Each
OR clause of a query like this can only use one index. So, it won't help this query to put separate indexes on
EXPLAIN result announces that the query planner is examining almost 34K rows, one by one, to find your result set. If your query used indexes,
EXPLAIN would tell you.
If you prefix your query with
EXPLAIN you'll get MySQL to give you some query-planner statistics.
Read this: http://use-the-index-luke.com/