user6461291 user6461291 - 5 months ago 19
MySQL Question

Simple query takes long time to execute

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:

SELECT *
FROM `posts`
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;


Here are the columns within this table:

http://i.imgur.com/w9qcpH2.png

I have not set any indexes on any of the columns if that helps. What should I do to make this query run faster?

Answer

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 title, content, and poster.

Edit: Your 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/

Comments