Strange pagination issue...

[ajlisowski]

Hi all, I am having a very odd issue with a pagination script I am running.

 

Basically I have php code which calls the following mysql query

 

SELECT  g.`owner`,  g.`name`,  g.`id` AS `default_index`  FROM `lunapr_custom_grid` AS `g`  ORDER BY g.`owner` DESC LIMIT 0,1

 

It correctly returns the result.

 

And as I go through the pages the limit changes to 1,1 and 2,1.

 

1,1 correctly returns the middle result.

 

However 2,1 incorrectly returns the 1st result again.

 

If I query the database with a limit of 0,3 I get the following:

 

public Basic Grid 10

public test3 22

public test2 23

 

0,1 returns: public test2 23

1,1 returns: public test3 22

2,1 returns: public test2 23

 

If I do not include desc it works fine.

 

ASC 0,3 returns the following:

 

public Basic Grid 10

public test3 22

public test2 23

 

ASC 0,1- public Basic Grid 10

ASC 1,1-public test3 22

ASC 2,1- public test2 23

 

So how come DESC 0,1 and 2,1 return the same result? Any ideas?

[andrewgauger]

I think it is a MySQL version issue that might have been fixed along the cycle:

 

http://bugs.mysql.com/bug.php?id=35605

 

Suggested solution: post your MySQL version.

[ajlisowski]

MySQL client version: 5.0.77

 

It appears as though this bug was fixed in 5.1.24 so it is probably what is causing it. I will check with my system admin to see if updating mysql is possible.

[luca200]

I can hardly see this as a bug.

You are providing a single field for ordering, and since you have more records than you need with the same value in that column, I think you should accept any random row containing that value.

And this is NOT the same issue as in the MySql bug that was linked before.

[ajlisowski]

Im not sure how MySql orders the results beyond the defined ORDER BY, but it does. It does not simply take a random value.

 

If I have 1000 rows with the same 'owner' field and I run a query with ORDER BY `owner` and no limits, it will return the exact same order every time, despite the fact they have the same order by value.

 

Heck, if I run a query with no ORDER BY, it will return the same ordering every time.

 

Thus, when grabbing portions of that query using a limit, I should be able to parse through the results.

[luca200]

Of course, getting a random value does NOT mean that mysql will shuffle results to provide you a different one each time!!

It simply uses an algorithm to get out your results, not taking care about ordering rows beyond the ordering fields you provided. This can easily mean that as long as you repeat the IDENTICAL query on the IDENTICAL table, you will get the same ordering. But as you change something (and adding a LIMIT clause you ARE changing something), the algorithm can change too, so the results will.

[andrewgauger]

The table is ordered from the beginning.  That order never changes.  The first entry you put in the table is the first entry in your table.  When you use ORDER BY you are starting from the top of the table and returning the values that are grouped by the order.  The only other reason for this behavior would be a shoddy index.

 

Please post this result:

 

SHOW INDEX FROM `lunapr_custom_grid`

[ajlisowski]

I simply have a primary key index on id and a non-unique index on owner

 

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

lunapr_custom_grid 0 PRIMARY 1 id A 3 NULL NULL   BTREE

lunapr_custom_grid 1 owner 1 owner A NULL NULL NULL   BTREE

 

Is the index on owner messing things up? I dont see how it could be.

 

EDIT:

 

Removing the index on order fixes the issue. However I do want that column indexed because in the future I will be running queries pulling rows based on the owner. so WHERE `owner`='whoever'

 

Any idea why my index is causing this to occur?

[andrewgauger]

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

 

I hope you can find the answer here, because I do not understand indexing very well.

[ajlisowski]

Well the problem appears to be related to the fact that its using the index for the order by. I simply changed the order by to `owner`,1 and not it can not use the index and it works fine. It would have been nice to be able to index the order by to speed things up, but oh well. Solved I suppose. Thanks for the help.