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
Pad Table
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)
.
精彩评论