开发者

mySQL how can I speed up with function field > x and field < y

This is my query

SELECT PadID FROM Pads WHERE ((RemoveMeDate='2001-01-01 00:00:00') 
AND (catid in (0,1,2,3,4,5,6,7,8,9,10,11)) AND ((ProgramName)<>'')) 
ORDER BY VersionAddDate DESC LIMIT 0,20;

Since I added

catID in (0,1,2,3,4,5,6,7,8,9,10,11)

Its slowed down a lot.

I've tried...

SELECT PadID FROM Pads WHERE RemoveMeDate='2001-01-01 00:00:00' AND 
catid >= 0 and catid <= 11 ORDER BY VersionAddDate DESC LIMIT开发者_如何学Python 0,20

But thats just a slow.

I thought I might be able to you coalesce but I'm not sure how ?

Any ideas ?

EDITS

Categories table

mySQL how can I speed up with function field > x and field < y

Pad Table

mySQL how can I speed up with function field > x and field < y


I guess you have to convince MySQL to filter on RemoveMeDate first. Try:

SELECT  PadID 
FROM    (
        SELECT  *
        FROM    Pads
        WHERE   RemoveMeDate = '2001-01-01 00:00:00' 
        ) as SubQuery
WHERE   catid >= 0 and catid <= 11 
ORDER BY 
        VersionAddDate DESC 
LIMIT   0, 20


Try using between

SELECT PadID FROM Pads WHERE RemoveMeDate='2001-01-01 00:00:00' AND 
catid between 0 and 11  ORDER BY VersionAddDate DESC LIMIT 0,20


You could use between clause instead of >= <=.

catid between ? and ?

After you could add index in RemoveDate and catid

ALTER TABLE Pads ADD INDEX RemoveDate (RemoveDate);


Create a composite index on (removemedate, catid).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜