…where 2 rows in set (0.00 sec) mysql> select * from information_schema.global_status where…tinyint, D_DayNumInMonth tinyint, D_DayNumInYear smallint, D_MonthNumInYear tinyint, D_WeekNumInYear tinyint, D_SellingSeason char(12), D_LastDayInWeekFl tinyint, D_LastDayInMonthFl tinyint, D_HolidayFl tinyint…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… NULL auto_increment, `name` varchar(32) NOT NULL, `has_something` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `has_something… rows (with random distribution) mysql> select cnt0/cnt from (select count(*) cnt0 from testr where has_something=0) t, (select count(*) cnt from testr) t1…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip… in key length in 5.0 explain. Actually I used tinyint for age and mediumint for zip which makes 4 right… for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name FROM people WHERE age…
Post: Logging Deadlock errors
September 19, 2012 By 4 Comments
…hold` char(1) NOT NULL, `victim` tinyint(3) unsigned NOT NULL, `query`…deadlock information in our table: mysql> select * from test.deadlocks\G *************************** 1. row ***************************…update City SET name=’New york2′ where name=’New York’ *************************** 2. row …
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…tinyint(4) NOT NULL, `t6` tinyint(4) NOT NULL, `t7` tinyint(4) NOT NULL, `t8` tinyint(4) NOT NULL, `t9` tinyint(4) NOT NULL, `t10` tinyint…you can use Null-Aware comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ 1 row …
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X…_id INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0, user_type TINYINT UNSIGNED NOT NULL DEFAULT 0, username… BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND user_id…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…dev/null 2>&1 < /dev/null done; Where to find …tinyint(4) DEFAULT NULL, `Month` tinyint(4) DEFAULT NULL, `DayofMonth` tinyint(4) DEFAULT NULL, `DayOfWeek` tinyint…2010′; mysql> use ontime1; Database changed mysql> show table…third parse time). $ echo “select count(*) from ontime_fact;” | ./run_query …
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… NOT NULL DEFAULT ’0000-00-00 00:00:00′, `type` tinyint(4) NOT NULL DEFAULT ’0′, KEY `id` (`id`), KEY `type… for a SIMPLE SELECT to see the actual count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where type=6 \G…: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type between 3 and…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… be able to see where this is going. For …20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_… being defined first. So, MySQL checks our INSERT, sees… the case. (root@localhost) [test]> select * from update_test; +————+———-+———+———————+ | id | username | host_id |…
Comment: GROUP_CONCAT useful GROUP BY extension
April 29, 2009 By 49 Comments
… is the code : —————— CREATE TABLE IF NOT EXISTS `user` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `nom` varchar(255) NOT… (0.00 sec) mysql> SELECT @lid; +——-+ | @lid | +——-+ | 4,6,7 | +——-+ 1 row in set (0.00 sec) mysql> SELECT * FROM user WHERE id IN(@lid…
