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.
Semisynchrounous replication - how to clear Rpl_semi_sync_master_no_tx/_times.
Hi all. I would like to clear values off Rpl_semi_sync_master_no_tx and Rpl_semi_sync_master_no_times status variables because I am plugging a sync replication setup into nagios. I've tried: mysql SET global Rpl_semi_sync_master_no_tx=0; ERROR 1193 (HY000): Unknown system variable 'Rpl_semi_sync_master_no_tx' mysql SET global Rpl_semi_sync_master_no_times=0; ERROR 1193 (HY000): Unknown system variable 'Rpl_semi_sync_master_no_times' Is there a way to clear these counters? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Opening .mwb file without MySql
Hi all-- I googled around a bunch but couldn't find a good answer to this question. How do I open the ERD diagram I made in MySQL WorkBench 5.2CE, which is a .mwb file-- on another computer that does not have MySQL on it? I want to be able to print out the file and so I need to be able to open it on the other computer. Would greatly appreciate any help you can provide. Thanks!
