get different result using select * and select askid

Question

That sounds wired,I find this occasionally when I try to optimize limit.

askid is PK and the table has 1.14M rows.
I can not give the table because it's in production environment.

I try

SELECT askid FROM ask limit 1000000,1;

return 1001747

Then I try

SELECT * FROM ask limit 1000000,1;

return 1000627

askid has index and it influence the result, but I think the result should be same,isn't it?

SELECT askid FROM ask ORDER BY askid LIMIT 1000000,1;

return 1000627.

But why ORDER BY matters?Is there something wrong with the askid index?

The performance is much different.
The first one takes 0.2s;
the second takes 2s;
the third one takes 1s;
How to optimize it and get the right result?

The mysql version I use is 5.6.10

Attempting to query using any positional clauses (ie `LIMIT`) is totally useless without `ORDER BY` — Phil, Aug 31 '15 at 03:15
possible duplicate of [MySQL row order for "SELECT \* FROM table\_name;"](http://stackoverflow.com/questions/1949641/mysql-row-order-for-select-from-table-name) — Phil, Aug 31 '15 at 03:16
@Phil But the performance is much different.The first one is at least 5 times faster than the third.Though the result is wrong. — fairjm, Aug 31 '15 at 03:21

StackSlave · Answer 1 · 2015-08-31T03:32:32.433

0

You have an OFFSET set to 1000000. Those queries are not the same. * SELECTs everything, not just your PRIMARY KEY. With the , after your LIMIT it becomes LIMIT offset, limit. Also, you should use ORDER BY askid on those queries so you have the correct order.

edited Aug 31 '15 at 03:32

answered Aug 31 '15 at 03:21

StackSlave

10,613
2
18
35

There is no rows existed without PK.Do you mean physical storage?I know little about this.... .... – fairjm Aug 31 '15 at 03:23
Is `askid` defined as `PRIMARY KEY(askid)` when you created the table? – StackSlave Aug 31 '15 at 03:28

score 0 · Answer 2 · answered Aug 31 '15 at 03:28

0

SQL makes no guarantees in regards to row order, it chooses whatever makes for the best plan based on your query.

If you expect an order, specify it using order by. And you most definitely should if you're limiting your result set.

answered Aug 31 '15 at 03:28

Blindy

65,249
10
91
131

score 0 · Answer 3 · answered Aug 31 '15 at 03:30

Data is stored in the natural order. This can be the order the rows are inserted or it can be the order the rows were updated, depending on the engine. It is NOT the PK order.

When you do not specify order by you are getting the natural order (for all intents and purposes it is random). You should not rely on it.

You can argue all you like, but you have already proved to yourself that the order by should be used.

get different result using select * and select askid

3 Answers3