Slow query with last record included

Hello,
I have a strange problem witha very easy query!
I have a MyIsam table, 150000 rows with company information and other values
for 50 fields total.
There is a primary key (code), an index on "companyname", table has been
analyzed and optimized.

I want to show 50 rows on every web page, and I have included button for
moving to previous, next, first and last page.
Query is:

SELECT * FROM contacts ORDER BY companyname LIMIT nnnn,50

It works fine, expect for the LAST page feature... the query took more than
30 seconds with hard drive 100% loaded!
The values passed to the LIMIT are correct, as the query ends and show the
right informations.

Example:

Select * from contacts order by companyname LIMIT 0,50 took 1 sec
Select * from contacts order by companyname LIMIT 130000,50 took 1 sec
Select * from contacts order by companyname LIMIT 149950,50 took 30 secs
Select * from contacts order by companyname LIMIT 149950,49 took 1 sec
(!!!!!!)

The problem is related to the last record of the table, if I limit the query
to the penultimate record it ends in 1 second!!
I really can't explain this...the platform used is Mysql 5.0.18 on Win32
(also with 4.0 same problem)

Thanks for your help!

Marco

Does it help to rewrite the query for the last page to:

SELECT * FROM contacts ORDER BY companyname DESC LIMIT 0,50

and displaying the results in opposite order?

Best regards,

Willem Bogaerts

BadWolf wrote:
> Hello,
> I have a strange problem witha very easy query!
> I have a MyIsam table, 150000 rows with company information and other values
> for 50 fields total.
> There is a primary key (code), an index on "companyname", table has been
> analyzed and optimized.
>
> I want to show 50 rows on every web page, and I have included button for
> moving to previous, next, first and last page.
> Query is:
>
> SELECT * FROM contacts ORDER BY companyname LIMIT nnnn,50
>
> It works fine, expect for the LAST page feature... the query took more than
> 30 seconds with hard drive 100% loaded!
> The values passed to the LIMIT are correct, as the query ends and show the
> right informations.
>
> Example:
>
> Select * from contacts order by companyname LIMIT 0,50 took 1 sec
> Select * from contacts order by companyname LIMIT 130000,50 took 1 sec
> Select * from contacts order by companyname LIMIT 149950,50 took 30 secs
> Select * from contacts order by companyname LIMIT 149950,49 took 1 sec
> (!!!!!!)
>
> The problem is related to the last record of the table, if I limit the query
> to the penultimate record it ends in 1 second!!
> I really can't explain this...the platform used is Mysql 5.0.18 on Win32
> (also with 4.0 same problem)
>
> Thanks for your help!
>
> Marco
>
>

"Dikkie Dik" <nospam@nospam.org> ha scritto nel messaggio
news:c38c9$440f4468$57d40752$32311@news.versatel.n l...
> Does it help to rewrite the query for the last page to:
>
> SELECT * FROM contacts ORDER BY companyname DESC LIMIT 0,50
>
> and displaying the results in opposite order?

It helps about the speed issue, but will display the record in reverse
order!
By the way, I think I solved my problem forcing Mysql to use the index
created on "companyname".

I don't know why, but also the EXPLAIN command confirmed that when the query
involve the last record the index is not used, and table is filesorted.
Even the query "SELECT * FROM contacts ORDER BY companyname" (without LIMIT)
is not using the index, why??



Thanks and best regards,
Marco