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]

Reply via email to