MySQL Query – Fixing Duplicate Records in Ordered Paginated Query

MySQLmysql-5.6order-byselect

Some records show up on multiple result "pages" with the following query:

SELECT 
    `description`,
    `wallpaper`
FROM
    `bbr_bar`
WHERE
    `country_id` = '2510769'
ORDER BY
    `online` DESC

For instance, record with id 99 shows up on the first "page" (LIMIT 10 OFFSET 0) but also on the last one LIMIT 10 OFFSET 50. Obviously this should not happen.

The problem disappears under the the following circumstances:

if I don't filter the query, or don't order it (both country_id and online are int(11), not foreign keys)
if I remove one of the two fields from the SELECT clause (both varchar(255), not foreign keys)
if I delete some tables or fields in the schema. However, afters hours of effort, I am still unable to say for sure which tables/fields have an impact and which ones don't. Looks like random (even if I know it is not). Hence I don't paste here any CREATE TABLE or INSERT statement as I don't think they are relevant.

Only thing I can say for sure is that if I mysqldump all tables and restore them in a new schema, the problem is still there.

(There are other fields in the table but adding/removing them has no effect on the result).

Questions:

Am I right if I conclude that something is corrupted in my schema?
How could I fix this?

(MySQL version: 5.6.24 Win32 x86)

Best Answer

I have some bad news for you: According to MySQL, IT'S NOT A BUG !!!

There are two bug reports on this behavior and is considered non-critical

MySQL 5.6 (Bug #69732 : LIMIT clause results in duplicate data across pages)
MySQL 5.5 (Bug #65307 : Problem with order by and limit offset)

Here is the rationale expressed in Bug #69732

Without a distinct ORDER BY the result order is undefined. Period.

Even if the same query, executed twice, results results in different order there is nothing wrong with that. You may be used to what looks like deterministic results but that is actually an illusion. It may be what you see in the usual case but there is nothing that guarantees this. Result order can change as statistics get updated, as index trees are reshuffled, as data is partitioned across different physical machines so that result order depends on network latencies ...

So if you want pagination then make sure you ORDER BY on something guaranteed to be UNIQUE, or live with the fact that the sort order of identical values in a non-unique sequence is not deterministic and can change at any time without prior notice.

In your particular case, the online field is probably not distinct enough. Even if you index bbr_bar by (country_id,online), this may still be not distinct enough. You may have to involve another column to drive the sort order (perhaps an auto increment field or a timestamp field).

Give it a try!!!!

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Best Answer

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Mysql – Help optimizing MySQL slow query

Related Question