Hi Avenger, Glad it's working well. :-)
Your index was slower than mine because the ArticleID column wasn't included in it at the end. While yours allowed MySQL to use it to find rows matching the WHERE and to resolve the ORDER BY without using filesort, MySQL would have to go to the data file to fetch ArticleID. This causes a lot of random disk seeks, especially when you're skipping over the first 1500000 rows! By adding ArticleID to the end, it still uses the index for WHERE and ORDER BY, but can additionally just grab the value of ArticleID out of the index. Hope that explains it. :-) Matt ----- Original Message ----- From: "Avenger" Sent: Wednesday, October 29, 2003 1:41 AM Subject: Re: Limit Optimization?? > That sooo cool... It was the very perfection of beauty. > > but i didnt' know.why my index (ClassID, Auditing, CreatedTime) are > slowly as matt's (ClassID, Auditing, CreatedTime, ArticleID) > > could matt explain why? > > Thx matt.. > > On Tue, 28 Oct 2003 02:06:16 -0600 > "Matt W" wrote: > > > Hi, > > > > OK, did you just create key1 and CreatedTime? If you didn't have them > > before and don't need them, you can remove them and just create this > > index for your query (you can definitely replace key1 with this): > > > > ALTER TABLE article > > ADD INDEX (ClassID, Auditing, CreatedTime, ArticleID); > > > > And then the query should only use the index for execution. Then you can > > of course run the second query to get all columns you want: > > > > SELECT * FROM article WHERE ArticleID IN (<Comma seperated list of > > ArticleIDs from first query>) ORDER BY CreatedTime; > > > > > > Hope that helps! > > > > > > Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
