Is it correct to scan a table in MySQL using “SELE

Question

Suppose Table X has a 100 tuples.

Will the following approach to scanning X generate all the tuples in TABLE X, in MySQL?

for start in [0, 10, 20, ..., 90]:
    print results of "select * from X LIMIT start, 10;"

I ask, because I've been using PostgreSQL, which clearly says that this approach need not work, but there seems to be no such info for MySQL. If it won't, is there a way to return results in a fixed ordering without knowing any other info about the table (like what the primary key fields are)?

I need to scan each tuple in a table in an application, and I want a way to do it without using too much memory in the application (so simply doing a "select * from X" is out).

Answer 1

No, that isn't a safe assumption. Without an ORDER BY clause, there is no guaranteeing that your query will return unique results each time. If this table is properly indexed, adding an ORDER BY (for the index) shouldn't be too expensive.

Edit: Non-ORDER BYed results will sometimes be in the order of the clustered index, but I wouldn't put any money on that!

Answer 2

If you are using Innodb or MyISAM table types, a better approach is to use the HANDLER interface. Only MySQL supports this, but it does what you want:

http://dev.mysql.com/doc/refman/5.0/en/handler.html

Also, the MySQL API supports two modes of retrieving data from the server:

1. store result: in this mode, as soon as a query is executed, the API retrieves the entire result set before returning to the user code. This can use up a lot of client memory buffering results, but minimises the use of resources on the server.
2. use result: in this mode, the API pulls results row-by-row and returns control to the user code more frequently. This minimises the use of memory on the client, but can hold locks on the server for longer.

Most of the MySQL APIs for various languages support this in oneform or another. It is usually an argument that can be supplied as when creating the connection, and / or a separate call that can be used against an existing connection to switch it to that mode.

So, in answer to your question - I would do the following:

set the connection to "use result" mode;
select * from X