Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;

The temp table is created at the beginning of the script having an "id" auto_increment column (and others). Part 1 of the script fills up the table using various SELECTs. Part 2 should process all selected rows in chunks of 1000 but will never ever write the table again.

I assume that these two commands are the same

SELECT FROM tt ORDER BY id LIMIT ?,1000;
SELECT FROM tt LIMIT ?,1000;

but is this true? Is there any chance that mySQL will retrive the rows in a different order on multiple SELECTs even if the table is not written in between?

Sorting the temp table is the longest part while reading it and I'd be happy to get rid of the ORDER BY part but I don't want to miss a record because the order changed between SELECT LIMIT 0,1000 and SELECT LIMIT 1000,1000.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

I guess this depends on the storage-engine. I can tell for MyISAM, a select without any order will return rows sequentially as they occur in file (even if held in memory).

As you ordered data during write, it will be ordered by id within MyISAM-temptables. So you can safely assume it will always return correct results as long as you don't delete from myISAM-temp.

But the new question is - is temptable created in myIsam-format? I assume the default storage engine will be used. This used to be MyISAM, but was changed to InnoDB after MySQL 5.1.

I don't know how innodb would handle an unsorted select.

-- edit

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

In big (on disk) temporary tables myISAM is used. It is not intentional behavior rows are served sequentially in order as written into, but it is by design (as documented) reliable as long as myISAM table is appended only.

This is most probably not true for any "hashed table" like InnoDB, BDB, and eventually MEMORY. But when memory table is converted to "on disk table", current order is "frozen on the disk", and therefore reliable again.

But I guess we have an other kind of problem here:

  • The 'real' problem seems to be your "order by" feels too slow, and you would like to get around that. Do you by chance know your table size and sessions max_heap_table_size? This give you a hint if your table has been converted to myisam. You can also play with Index-Hash-method (BTREE, HASH) to look for optimization.

  • Are you aware that LIMIT N,M will get painfully slow because it has to select N rows, discard them, and then select M rows to return?

share|improve this answer
    
All tables are MyISAM but I don't know if the temp table also is. Maybe I should force it to use MyISAM. Thank you for your answer, as the final order doesn't matter at all (as long as each row is processed once), a sequential order is perfectly ok for me. –  Sebastian Feb 26 '12 at 21:06
    
I don't think MEMORY tables are MyISAM, in any case you should never assume anything about order when not using ORDER BY. What you can do is create an INDEX (PRIMARY KEY) on id. I think that could speed it up a little. If not you can also create a covering index (PRIMARY KEY) with ALL fields in it (id first). In that case MySQL should be doing the "using index" optimization for the ORDER BY and get all SELECTed values from the index instead of looking them up in the table. Doubles you memory usage for this table I guess. –  Jannes Feb 27 '12 at 2:11
    
The table's id column is auto_increment and the primary_key index. –  Sebastian Feb 27 '12 at 8:47
2  
I will just repeat Jannes's recommendation: Never assume anything about the order of your result set if you do not use ORDER BY. If you do you are relying on unspecified behavior. Without ORDER BY, MySQL is free to order the rows differently between calls for any reason. –  Nick Chammas Feb 27 '12 at 20:12

This isn't directly related to ORDER BY, but be careful using the LIMIT N,M pattern. This is often used for paginating results, e.g.

... limit 0,10;
... limit 10,10;
... limit 20,10;

Each time through mysql will "select" the first N rows and just return the last M to the client. As you go deeper and deeper you're selecting out more and more of rows, only to be thrown away.

In short each "page" takes longer to load. If your datasets are small and queries are using indexes well this may seem inconsequential, but the effect will become evident as you grow. I feel it's generally better to put these constraints in the where clause. e.g.

... where id between 0 and 10;
... where id between 11 and 20;
... where id between 21 and 30;

Finally, if you're worried about the ordering to the limiting effectively creates the correct range restrictions for you then this concern is completely eliminated to begin with.

share|improve this answer

Instead of your original query

SELECT FROM tt ORDER BY id LIMIT ?,1000;

you may want to refactor the query tp apply the order by against the keys only and join the keys back to the original table:

SELECT tt.*
FROM
(
    SELECT id FROM tt
    ORDER BY id LIMIT ?,1000
) ttkeys
LEFT JOIN tt USING (id);

This can be done to force 1000 keys only to join against the original table.

I use LEFT JOIN instead of INNER JOIN because LEFT JOIN keeps the order of the keys after the join is complete, whereas INNER JOIN tends to undo the ordering in the subquery.

If doing this against a internal temp table has issues, then try it with an external temp table as follows:

CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
CREATE TEMPORARY TABLE ttkeys
    SELECT id FROM tt
    ORDER BY id LIMIT 1000
;
ALTER TABLE ttkeys ADD PRIMARY KEY (id);
SELECT tt.* FROM ttkeys
LEFT JOIN tt USING (id);
share|improve this answer
    
Subselects are broken with MySQL. At least in older versions of it. So be careful with that. –  tex Feb 28 '12 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.