…SYS_COLUMNS` ( `TABLE_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `NAME` varchar(255) DEFAULT NULL, `MTYPE` int(10) unsigned DEFAULT NULL, `…where it would make sense it is still zero. If you know how InnoDB uses `PREC` please let me know. mysql> select * from SYS_COLUMNS WHERE…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL\G *************************** 1… where the REFERENCED_* columns are non-null. From that information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he ran this query: SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time…:00:00 | +———————+ Very bizarre. On one hand, it seems like MySQL is using date windowing for two-digit dates (this would explain…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… case where I will add a new column to the table while a transaction in REPEATABLE-READ isolation mode is ACTIVE. session1 > select @@tx… where i=1 for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
…, but not COUNT(val). Why ? Because val column is not defined as NOT NULL there can be some NULL values in it and so MySQL have…mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count(val) from fact where i select…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…column `has_something` (the column has two values 0 and 1). In reality the right answer is not…is faster. And finally for case with 20% rows with has_someting=0 mysql> select count(name) from testr force key (has_something) where…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…where this is not useful, simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…WHERE role_id = 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE `cast_info` ( `id` int(11) NOT NULL…column) and can take advantage of the covering index technique, did actually take more time to execute: mysql> SELECT…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL is…is not what you’re looking for. Q: I have a primary index on an int (ID) and other indexes on columns…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE…MySQL is able to execute it much faster, delivering us expected performance. You can also use this approach when first key column is not in where…
