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]
