Re: Query optimizer-miss with unqualified expressions, bug or feature?
Hi Shawn, On 19.10.15 22.33, shawn l.green wrote: On 10/19/2015 3:48 PM, Roy Lyseng wrote: Hi Ben, On 19.10.15 16.07, Ben Clewett wrote: Hi Roy, Thanks for the clear explanation. I guess (hypothetically) the optimizer could see if it has a key, and then use two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result? Which might make a significant result to something? That is correct. However, if the substitution type for BOOLEAN was UNSIGNED TINYINT instead of TINYINT, the range 'a < 0' would not even be needed, and the quite cumbersome UNION would be avoided. But the best solution would of course be a two-valued boolean type, where 'a <> 0' would easily be transformed to a = 1. It would also mean that statistics for the columns would be better, with TINYINT each value has the estimated probability 1/256, whereas a boolean value would have probability 1/2. 256 possible values of BOOLEAN? I've heard of fuzzy logic but this is awesome! Some new literal value names to consider: maybe, sort_of, nearly_always, certainly, practically_never, likely, ... * Well, it is the practical consequence of using TINYINT as the substitution type for BOOLEAN... On a more serious note, indexes with limited cardinality are less useful than those with excellent cardinality. Cardinality is an approximation (or calculation. It depends on your storage engine) of how many unique values there are in the index. If the Optimizer estimates (based on a calculation based on the Cardinality) that more than about 30% of a table would need to be retrieved in random order based on an index, then that index is disallowed. Why? Because the physical disk overhead of doing random access averages just slightly more than 3x the overhead used to scan a much larger block of data. http://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html An index on just a Boolean value would have at best a cardinality of 2. So, any indexes on Boolean values should include other columns to help the index become more selective. http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html You are right about the index use, so it would be interesting only with a significant skew, say 10% TRUE values. However, the optimizer is not only about indexing, but also about calculating the filtering effect of a predicate. Using a true BOOLEAN rather than a TINYINT would give a better estimate of the filtering effect, and thus of the estimated number of rows as the outcome of a query. *Actually, fuzzy logic has lots of practical application in real world situations. They are just not using the MySQL BOOLEAN data type to store the value for comparison. Thanks, Roy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query optimizer-miss with unqualified expressions, bug or feature?
On 10/19/2015 3:48 PM, Roy Lyseng wrote: Hi Ben, On 19.10.15 16.07, Ben Clewett wrote: Hi Roy, Thanks for the clear explanation. I guess (hypothetically) the optimizer could see if it has a key, and then use two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result? Which might make a significant result to something? That is correct. However, if the substitution type for BOOLEAN was UNSIGNED TINYINT instead of TINYINT, the range 'a < 0' would not even be needed, and the quite cumbersome UNION would be avoided. But the best solution would of course be a two-valued boolean type, where 'a <> 0' would easily be transformed to a = 1. It would also mean that statistics for the columns would be better, with TINYINT each value has the estimated probability 1/256, whereas a boolean value would have probability 1/2. 256 possible values of BOOLEAN? I've heard of fuzzy logic but this is awesome! Some new literal value names to consider: maybe, sort_of, nearly_always, certainly, practically_never, likely, ... * On a more serious note, indexes with limited cardinality are less useful than those with excellent cardinality. Cardinality is an approximation (or calculation. It depends on your storage engine) of how many unique values there are in the index. If the Optimizer estimates (based on a calculation based on the Cardinality) that more than about 30% of a table would need to be retrieved in random order based on an index, then that index is disallowed. Why? Because the physical disk overhead of doing random access averages just slightly more than 3x the overhead used to scan a much larger block of data. http://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html An index on just a Boolean value would have at best a cardinality of 2. So, any indexes on Boolean values should include other columns to help the index become more selective. http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html *Actually, fuzzy logic has lots of practical application in real world situations. They are just not using the MySQL BOOLEAN data type to store the value for comparison. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query optimizer-miss with unqualified expressions, bug or feature?
Hi Ben, On 19.10.15 16.07, Ben Clewett wrote: Hi Roy, Thanks for the clear explanation. I guess (hypothetically) the optimizer could see if it has a key, and then use two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result? Which might make a significant result to something? That is correct. However, if the substitution type for BOOLEAN was UNSIGNED TINYINT instead of TINYINT, the range 'a < 0' would not even be needed, and the quite cumbersome UNION would be avoided. But the best solution would of course be a two-valued boolean type, where 'a <> 0' would easily be transformed to a = 1. It would also mean that statistics for the columns would be better, with TINYINT each value has the estimated probability 1/256, whereas a boolean value would have probability 1/2. Thanks, Roy Ben. On 2015-10-19 14:19, Roy Lyseng wrote: Hi Ben, On 19.10.15 15.10, Ben Clewett wrote: I have noticed that an unqualified boolean expression cannot be optimized by MySQL to use an index in 5.6.24. For example: CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a BOOLEAN NOT NULL, KEY a (a) ) ENGINE=InnoDB; This will hit key 'a': SELECT * FROM t WHERE a = TRUE; This will return the same results, but not use key 'a': SELECT * FROM t WHERE a; Is this a bug, or deliberate behaviour, or a missing feature, or perhaps something else? MySQL does not have a true boolean type, so this is actually interpreted as SELECT * FROM t WHERE a <> 0; The optimizer is not able to see that "a <> 0" means "a = 1", and hence no index will be used. Thanks, Roy Thanks, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Cluster 7.2.22 has been released
Dear MySQL Users, MySQL Cluster is the distributed, shared-nothing variant of MySQL. This storage engine provides: - In-Memory storage - Real-time performance (with optional checkpointing to disk) - Transparent Auto-Sharding - Read & write scalability - Active-Active/Multi-Master geographic replication - 99.999% High Availability with no single point of failure and on-line maintenance - NoSQL and SQL APIs (including C++, Java, http and Memcached) MySQL Cluster 7.2.22, has been released and can be downloaded from http://www.mysql.com/downloads/cluster/ where you will also find Quick Start guides to help you get your first MySQL Cluster database up and running. The release notes are available from http://dev.mysql.com/doc/relnotes/mysql-cluster/7.2/en/index.html MySQL Cluster enables users to meet the database challenges of next generation web, cloud, and communications services with uncompromising scalability, uptime and agility. More details can be found at http://www.mysql.com/products/cluster/ Enjoy ! == Changes in MySQL Cluster NDB 7.2.22 (5.5.46-ndb-7.2.22) (2015-10-19) MySQL Cluster NDB 7.2.22 is a new release of MySQL Cluster, incorporating new features in the NDB storage engine, and fixing recently discovered bugs in previous MySQL Cluster NDB 7.2 development releases. Obtaining MySQL Cluster NDB 7.2. MySQL Cluster NDB 7.2 source code and binaries can be obtained from http://dev.mysql.com/downloads/cluster/. This release also incorporates all bugfixes and changes made in previous MySQL Cluster releases, as well as all bugfixes and feature changes which were added in mainline MySQL 5.5 through MySQL 5.5.46 (see Changes in MySQL 5.5.46 (2015-09-30) (http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-46.html)). Bugs Fixed * Backup block states were reported incorrectly during backups. (Bug #21360188) References: See also Bug #20204854, Bug #21372136. * When a data node is known to have been alive by other nodes in the cluster at a given global checkpoint, but its sysfile reports a lower GCI, the higher GCI is used to determine which global checkpoint the data node can recreate. This caused problems when the data node being started had a clean file system (GCI = 0), or when it was more than more global checkpoint behind the other nodes. Now in such cases a higher GCI known by other nodes is used only when it is at most one GCI ahead. (Bug #19633824) References: See also Bug #20334650, Bug #2183. This bug was introduced by Bug #29167. * After restoring the database schema from backup using ndb_restore, auto-discovery of restored tables in transactions having multiple statements did not work correctly, resulting in Deadlock found when trying to get lock; try restarting transaction errors. This issue was encountered both in the mysql client, as well as when such transactions were executed by application programs using Connector/J and possibly other MySQL APIs. Prior to upgrading, this issue can be worked around by executing SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'NDBCLUSTER' on all SQL nodes following the restore operation, before executing any other statements. (Bug #18075170) * ndb_desc used with the --extra-partition-info and --blob-info options failed when run against a table containing one or more TINYBLOB. columns. (Bug #14695968) * Cluster API: The internal value representing the latest global checkpoint was not always updated when a completed epoch of event buffers was inserted into the event queue. This caused subsequent calls to Ndb::pollEvents() and pollEvents2() to fail when trying to obtain the correct GCI for the events available in the event buffers. This could also result in later calls to nextEvent() or nextEvent2() seeing events that had not yet been discovered. (Bug #78129, Bug #21651536) * Cluster API: While executing dropEvent(), if the coordinator DBDICT failed after the subscription manager (SUMA block) had removed all subscriptions but before the coordinator had deleted the event from the system table, the dropped event remained in the table, causing any subsequent drop or create event with the same name to fail with NDB error 1419 Subscription already dropped or error 746 Event name already exists. This occurred even when calling dropEvent() with a nonzero force argument. Now in such cases, error 1419 is ignored, and DBDICT deletes the event from the table. (Bug #21554676) -- MySQL General Mailing List For list archives: http://list
MySQL Cluster 7.4.8 has been released
Dear MySQL Users, MySQL Cluster 7.4.8 (General Availability) is a new release for MySQL Cluster 7.4. MySQL Cluster is the distributed, shared-nothing variant of MySQL. This storage engine provides: - In-Memory storage - Real-time performance (with optional checkpointing to disk) - Transparent Auto-Sharding - Read & write scalability - Active-Active/Multi-Master geographic replication - 99.999% High Availability with no single point of failure and on-line maintenance - NoSQL and SQL APIs (including C++, Java, http, Memcached and JavaScript/Node.js) MySQL Cluster 7.4 makes significant advances in performance; operational efficiency (such as enhanced reporting and faster restarts and upgrades) and conflict detection and resolution for active-active replication between MySQL Clusters. MySQL Cluster 7.4.8 DMR can be downloaded from the "Development Releases" tab at http://www.mysql.com/downloads/cluster/ where you will also find Quick Start guides to help you get your first MySQL Cluster database up and running. The release notes are available from http://dev.mysql.com/doc/relnotes/mysql-cluster/7.4/en/index.html MySQL Cluster enables users to meet the database challenges of next generation web, cloud, and communications services with uncompromising scalability, uptime and agility. As with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. More information on the Development Milestone Release process can be found at http://dev.mysql.com/doc/mysql-development-cycle/en/development-milestone-releases.html More details can be found at http://www.mysql.com/products/cluster/ Enjoy ! Changes in MySQL Cluster NDB 7.4.8 (5.6.27-ndb-7.4.8 2015-10-16) MySQL Cluster NDB 7.4.8 is a new release of MySQL Cluster 7.4, based on MySQL Server 5.6 and including features in version 7.4 of the NDB storage engine, as well as fixing recently discovered bugs in previous MySQL Cluster releases. Obtaining MySQL Cluster NDB 7.4. MySQL Cluster NDB 7.4 source code and binaries can be obtained from http://dev.mysql.com/downloads/cluster/. For an overview of changes made in MySQL Cluster NDB 7.4, see MySQL Cluster Development in MySQL Cluster NDB 7.4 (http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-develop ment-5-6-ndb-7-4.html). This release also incorporates all bugfixes and changes made in previous MySQL Cluster releases, as well as all bugfixes and feature changes which were added in mainline MySQL 5.6 through MySQL 5.6.27 (see Changes in MySQL 5.6.27 (2015-09-30) (http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-27.h tml)). Functionality Added or Changed * Important Change; Cluster Replication: Added the create_old_temporals server system variable to compliment the system variables avoid_temporal_upgrade and show_old_temporals introduced in MySQL 5.6.24 and available in MySQL Cluster beginning with NDB 7.3.9 and NDB 7.4.6. Enabling create_old_temporals causes mysqld to use the storage format employed prior to MySQL 5.6.4 when creating any DATE, DATETIME, or TIMESTAMP column---that is, the column is created without any support for fractional seconds. create_old_temporals is disabled by default. The system variable is read-only; to enable the use of pre-5.6.4 temporal types, set the equivalent option (--create-old-temporals) on the command line, or in an option file read by the MySQL server. create_old_temporals is available only in MySQL Cluster; it is not supported in the standard MySQL 5.6 server. It is intended to facilitate upgrades from MySQL Cluster NDB 7.2 to MySQL Cluster NDB 7.3 and 7.4, after which table columns of the affected types can be upgraded to the new storage format. create_old_temporals is deprecated and scheduled for removal in a future version of MySQL Cluster. avoid_temporal_upgrade must also be enabled for this feature to work properly. You should also enable show_old_temporals as well. For more information, see the descriptions of these variables. For more about the changes in MySQL's temporal types, see Storage Requirements for Date and Time Types (http://dev.mysql.com/doc/refman/5.6/en/storage-requireme nts.html#data-types-storage-reqs-date-time). (Bug #20701918) References: See also Bug #21492598, Bug #72997, Bug #18985760. * When the --database option has not been specified for ndb_show_tables, and no tables are found in the TEST_DB database, an appropriate warning message is now issued. (Bug #78379, Bug #11758430) Bugs Fixed * Important Change: When ndb_restore was run without --disable-indexes or --rebuild-indexes on a table having
Re: a
No, he's the one who already receives a ton of email and could do without "conversations" like this. I'm also in that boat. sent from my mobile On Oct 19, 2015 7:41 PM, "Ryan Coleman" wrote: > You’re the one in grade school that always reminded us the teacher might > be coming back soon and we should behave, right? > > > > On Oct 18, 2015, at 4:40 PM, Reindl Harald > wrote: > > > > what about stop that bullshit or at least purge the list after press > reply-all? > > > > Am 18.10.2015 um 22:52 schrieb bluethu...@gmail.com: > >> e > >> > >> Sent from my iPhone > >> > >>> On Oct 18, 2015, at 4:12 PM, Daevid Vincent wrote: > >>> > >>> d > >>> > -Original Message- > From: ryan.esca...@gmail.com [mailto:ryan.esca...@gmail.com] On > Behalf > Of Ryan Escarez > Sent: Friday, October 16, 2015 2:47 AM > To: Ryan Coleman > Cc: Dennis Ruiz; mysql-le...@lists.mysql.com > Subject: Re: a > > c > > On Fri, Oct 16, 2015 at 3:01 PM, Ryan Coleman > wrote: > > > b > > > >> On Oct 15, 2015, at 10:07 PM, Dennis Ruiz > wrote: > >> > >> a > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
Re: Query optimizer-miss with unqualified expressions, bug or feature?
Hi Roy, Thanks for the clear explanation. I guess (hypothetically) the optimizer could see if it has a key, and then use two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result? Which might make a significant result to something? Ben. On 2015-10-19 14:19, Roy Lyseng wrote: Hi Ben, On 19.10.15 15.10, Ben Clewett wrote: I have noticed that an unqualified boolean expression cannot be optimized by MySQL to use an index in 5.6.24. For example: CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a BOOLEAN NOT NULL, KEY a (a) ) ENGINE=InnoDB; This will hit key 'a': SELECT * FROM t WHERE a = TRUE; This will return the same results, but not use key 'a': SELECT * FROM t WHERE a; Is this a bug, or deliberate behaviour, or a missing feature, or perhaps something else? MySQL does not have a true boolean type, so this is actually interpreted as SELECT * FROM t WHERE a <> 0; The optimizer is not able to see that "a <> 0" means "a = 1", and hence no index will be used. Thanks, Roy Thanks, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a
You’re the one in grade school that always reminded us the teacher might be coming back soon and we should behave, right? > On Oct 18, 2015, at 4:40 PM, Reindl Harald wrote: > > what about stop that bullshit or at least purge the list after press > reply-all? > > Am 18.10.2015 um 22:52 schrieb bluethu...@gmail.com: >> e >> >> Sent from my iPhone >> >>> On Oct 18, 2015, at 4:12 PM, Daevid Vincent wrote: >>> >>> d >>> -Original Message- From: ryan.esca...@gmail.com [mailto:ryan.esca...@gmail.com] On Behalf Of Ryan Escarez Sent: Friday, October 16, 2015 2:47 AM To: Ryan Coleman Cc: Dennis Ruiz; mysql-le...@lists.mysql.com Subject: Re: a c On Fri, Oct 16, 2015 at 3:01 PM, Ryan Coleman wrote: > b > >> On Oct 15, 2015, at 10:07 PM, Dennis Ruiz wrote: >> >> a > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query optimizer-miss with unqualified expressions, bug or feature?
Hi Ben, On 19.10.15 15.10, Ben Clewett wrote: I have noticed that an unqualified boolean expression cannot be optimized by MySQL to use an index in 5.6.24. For example: CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a BOOLEAN NOT NULL, KEY a (a) ) ENGINE=InnoDB; This will hit key 'a': SELECT * FROM t WHERE a = TRUE; This will return the same results, but not use key 'a': SELECT * FROM t WHERE a; Is this a bug, or deliberate behaviour, or a missing feature, or perhaps something else? MySQL does not have a true boolean type, so this is actually interpreted as SELECT * FROM t WHERE a <> 0; The optimizer is not able to see that "a <> 0" means "a = 1", and hence no index will be used. Thanks, Roy Thanks, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Query optimizer-miss with unqualified expressions, bug or feature?
I have noticed that an unqualified boolean expression cannot be optimized by MySQL to use an index in 5.6.24. For example: CREATE TABLE t ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a BOOLEAN NOT NULL, KEY a (a) ) ENGINE=InnoDB; This will hit key 'a': SELECT * FROM t WHERE a = TRUE; This will return the same results, but not use key 'a': SELECT * FROM t WHERE a; Is this a bug, or deliberate behaviour, or a missing feature, or perhaps something else? Thanks, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
