Offset and limit Mysql problem

Question

When performing a query using limit and offset the database returns a line less than it should. What’s the problem? Cache? Lock? Index? Dice?

For example:

select distinct(id_table1) from table_1
join table_2 on table_2.id_table2 = table1.id_table2
where table2.campo = numero_qualquer
limit 100
offset 523800;

The above query returns 99 results instead of 100.

If I do the same query with limit 1 and offset 523842 returns 0 results. Note that if I increase the limit to 2 returns 1. I do not know why this line is not displayed it is apparently counted by the offset to jump but is not displayed in the limit result.

Tables are in Myisam, I’ve noticed index and data and nothing works.

Answer 1

The problem is that when you make one LIMIT with GROUP_BY or DISTINCT the OFFSET changes according to the created grouping. then the id 523800 does not reference his position in TABELA and yes its position in the grouping, so when the grouping was created, you may no longer have 100 records from that index but only 99.

Try to increase the number of LIMIT for 200, it will continue to be 99.