Re: using LIMIT without ORDER BY
- Original Message - From: Akshay Suryavanshi akshay.suryavansh...@gmail.com I am not sure, but if its a MyISAM table, it should be ordered by the records insertion order, and in case of InnoDB it should be ordered by the clustered index, not necessarily it should be a defined one. No. The optimizer may choose to do a full table scan, or it may choose to use an index scan. That decision may change due to changes in the data, or because the next version of mysql you upgrade to has different (and hopefully better...) alghorithms, et cetera. The ONLY way to ensure consecutive queries return your data in the same order, is specifying an order by clause. Apart from that, I personally prefer to avoid the limit 0,10 /limit 11/20 technique, because a) rows might have gotten inserted and/or deleted, and b) limit is applied to the full resultset. Instead, order by the PK (or another unique index or combination of indices), remember the last record's value(s) and use that as starting point for your next query. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using LIMIT without ORDER BY
Well Johan, I was referring to a condition when there is no index on the tables, not even primary keys. Your explanation makes complete sense about the optimizer and the pagination queries. Thanks, Akshay S On Thu, Dec 13, 2012 at 2:34 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Akshay Suryavanshi akshay.suryavansh...@gmail.com I am not sure, but if its a MyISAM table, it should be ordered by the records insertion order, and in case of InnoDB it should be ordered by the clustered index, not necessarily it should be a defined one. No. The optimizer may choose to do a full table scan, or it may choose to use an index scan. That decision may change due to changes in the data, or because the next version of mysql you upgrade to has different (and hopefully better...) alghorithms, et cetera. The ONLY way to ensure consecutive queries return your data in the same order, is specifying an order by clause. Apart from that, I personally prefer to avoid the limit 0,10 /limit 11/20 technique, because a) rows might have gotten inserted and/or deleted, and b) limit is applied to the full resultset. Instead, order by the PK (or another unique index or combination of indices), remember the last record's value(s) and use that as starting point for your next query. -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: using LIMIT without ORDER BY
- Original Message - From: Akshay Suryavanshi akshay.suryavansh...@gmail.com I was referring to a condition when there is no index on the tables, not even primary keys. If you have a lot of data in there, may I suggest you (temporarily) add a unique index and benchmark both methods? As I said, limit n,m is the last operation that gets executed, so it requires rows 0-n to be fetched, too. On deep pages that can be quite a lot of data needlessly fetched. You might find that the expense of maintaining that index may be well worth the speedup when fetching pages. Your explanation makes complete sense about the optimizer and the pagination queries. Thanks, You're welcome. -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: using LIMIT without ORDER BY
I am not sure, but if its a MyISAM table, it should be ordered by the records insertion order, and in case of InnoDB it should be ordered by the clustered index, not necessarily it should be a defined one. On Thu, Dec 13, 2012 at 12:58 PM, jiangwen jiang jiangwen...@gmail.comwrote: hi, all: There's a confusion. I want to get all the data in table t by pages, using Limit SQL without ORDER BY: SELECT * FROM t Limit 0,10 SELECT * FROM t Limit 10, 10 ... Is it right without ORDER BY? Is there any default order in table t, to make suer I can get all data in the table? Thanks Regards! White
