Hi, That's good! :-)
Are you saying that you need to have a WHERE clause on the first "... LIMIT 1500000, 20" query? Can you give an example of the full query, with the WHERE etc.? We'll see which columns can or should be added to an index. Also, can you show which indexes are already on the table? The last lines of "SHOW CREATE TABLE table;" with the KEY definitions is fine. Matt ----- Original Message ----- From: "avenger" Sent: Monday, October 27, 2003 10:24 PM Subject: Re: Limit Optimization?? > good job!! > it short my query time from 30 sec to 0.6 sec. > IOW,now i can not use the 'where' & 'order by' clause in the SELECT . can i > need more indexs ? > > thx Matt. > > > ""Matt W"" <[EMAIL PROTECTED]> wrote > [EMAIL PROTECTED] > > Hi, > > > > Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long > > as filesort isn't used for an ORDER BY. But your LIMIT 1500000, 20 will > > take much longer (assuming filesort isn't used) than, say, LIMIT 1000, > > 20. This is because it has to scan over 1.5M rows first. It's not really > > possible to just "start" at row 1500000. > > > > If you're not joining another table or something where it first needs to > > know how many rows from the table match, you can do something like this > > with 2 queries in your code. The first just scans the index which is > > much faster than scanning the data file. > > > > SELECT id FROM table ORDER BY id LIMIT 1500000, 20; > > > > Then take the first and last of those ids and run this query to get the > > other columns: > > > > SELECT * FROM table WHERE id BETWEEN @low_id AND @high_id ORDER BY id; > > > > > > Hope that helps. > > > > > > Matt > > > > > > ----- Original Message ----- > > From: "avenger" > > Sent: Monday, October 27, 2003 7:57 PM > > Subject: Limit Optimization?? > > > > > > > Does mysql do any optimization for then one use > > > > > > `select ... limit x,y`? > > > > > > For example, I have table with 2000000 records and want to do page web > > > interface to this table. > > > > > > When i use `select ... from table limit 1500000, 20 `, it will need > > more and > > > more times (on my here is more than 60 sec). > > > > > > well,that is sooooo slowly for the web interface. can any case make it > > > quickly.... > > > > > > help,plz. Thx for all.... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
