CODEWITHSUNDEEP
mysql
Angus Johnston
Angus Johnston

Reputation: 45

How can I make this query run faster?

I'm just wondering if theres anything I could do to make the query run a bit faster?

I have this query: 

SELECT * FROM posts
WHERE posts.exists = 'n'
ORDER BY posts.ratecount DESC
LIMIT 0,100

Takes: 2.47s @ 0,100 | 6.18s @ 500,100

This works but it tends to get rather slow as the limit increases (100,100 > 200,100 etc).

Using an index doesn't seem to help either: 

SELECT * FROM posts
USE INDEX(ratecount_ca)
WHERE posts.exists = 'n'
ORDER BY posts.ratecount DESC
LIMIT 0,100

Takes: 8.59s @ 0,100 | 28.98s @ 500,100

Strangely enough, without the WHERE it works perfect.

A descending index would most likely fix the issue but since that doesn't seem to be implemented yet, I need another option. Doing the WHERE after it's ordered would mostly likely speed it up too, but since I'm rather new to SQL I have no idea how to do :<

Upvotes: 0

Views: 116

Answers (2)

Tomasz Badowiec
Tomasz Badowiec

Reputation: 51

What version of MySQL are you using? v5.0 docs suggest there is a way to change index order.

I would suggest to create index on both columns. It should look something like:

CREATE INDEX index_2_cols ON posts(exists, ratecount DESC);

Upvotes: 1

KARASZI Istv&#225;n
KARASZI Istv&#225;n

Reputation: 31467

Since you said that omitting the where condition makes it run fast, I would try to create an index on exists field of the posts table:

CREATE INDEX ON posts_exists ON posts (exists(10));

Upvotes: 0

Related Questions