PHWinfo - Afficher un message

11/04/2008, 03h33

>>>> > Lookup the LIMIT feature in the MySQL manual.
>>>> > John
>>>>
>>>> Does anyone know how LIMIT actually works in practice. I would assume
>>>> that the first read would return the first X records starting at record
>>>> 1. When the second read happens does the server physically start at X+1
>>>> and return X records or does it actually re-read the first X records to
>>>> get to the starting point? And the third read? I guess what I'm getting
>>>> at is MySQL
>>
>> It does need to read the records to the extent of figuring out whether
>> they are to be selected, or to sort them to determine order.
>> If the query cache can't be used, it needs to do this on each query.
>>
>>>> smart enough to remember where the last access finished (and if so, how)
>>>> or do we end up reading from the start of the table each time?
>>
>> MySQL is smart enough to NOT make the result of one query depend on
>> invisible variables set by previous queries. LIMIT is not a cursor.
>>
>
>Thats what I suspected, which, in conjunction with the fact that the
>database could change between requests, makes me wonder what you'd actually
>use LIMIT for?

If you want a Top Ten list (or just Top One) of something, it works fine. Just don't ask for the Second Ten.

You can use LOCK TABLES if you need to, but that blocks updates.
I think transactions will also work for InnoDB tables.

Things may change so slowly you don't care. Does missing one entry
in team rankings really matter when the new game scores are only
entered once a week?

Things may change so quickly you don't care. If the list of the Top 10
Most Volatile Stocks usually changes faster than you can refresh the page,
does it really matter if you get 11-20 or 10-19?

Sometimes up-to-date data matters more than an ordering.

Some databases forbid deletions, or the data just doesn't have many
deletions. How many USA states have been deleted in the last few
decades?

If it is likely that you (the user) want to compare adjacent entries,
don't paginate by full pages. For example, show 1-8, then 6-13,
then 11-18, so there is deliberate overlap between pages.

11/04/2008, 03h33	#13
Gordon Burditt Messages: n/a Hébergeur:	Re: Getting data in chunks. >>>> > Lookup the LIMIT feature in the MySQL manual. >>>> > John >>>> >>>> Does anyone know how LIMIT actually works in practice. I would assume >>>> that the first read would return the first X records starting at record >>>> 1. When the second read happens does the server physically start at X+1 >>>> and return X records or does it actually re-read the first X records to >>>> get to the starting point? And the third read? I guess what I'm getting >>>> at is MySQL >> >> It does need to read the records to the extent of figuring out whether >> they are to be selected, or to sort them to determine order. >> If the query cache can't be used, it needs to do this on each query. >> >>>> smart enough to remember where the last access finished (and if so, how) >>>> or do we end up reading from the start of the table each time? >> >> MySQL is smart enough to NOT make the result of one query depend on >> invisible variables set by previous queries. LIMIT is not a cursor. >> > >Thats what I suspected, which, in conjunction with the fact that the >database could change between requests, makes me wonder what you'd actually >use LIMIT for? If you want a Top Ten list (or just Top One) of something, it works fine. Just don't ask for the Second Ten. You can use LOCK TABLES if you need to, but that blocks updates. I think transactions will also work for InnoDB tables. Things may change so slowly you don't care. Does missing one entry in team rankings really matter when the new game scores are only entered once a week? Things may change so quickly you don't care. If the list of the Top 10 Most Volatile Stocks usually changes faster than you can refresh the page, does it really matter if you get 11-20 or 10-19? Sometimes up-to-date data matters more than an ordering. Some databases forbid deletions, or the data just doesn't have many deletions. How many USA states have been deleted in the last few decades? If it is likely that you (the user) want to compare adjacent entries, don't paginate by full pages. For example, show 1-8, then 6-13, then 11-18, so there is deliberate overlap between pages.