…. Now, I understand that my PRIMARY KEY (`hid`,`mid`,`id`) where `id` is sequential, and `hid`,`mid` is low selectivity is…=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… only row we really need is with 2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | type | possible… expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… following query: mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name=’Music… -A Database changed mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
… instance, counting the number of sleeping connections can be done with: mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND=’Sleep’; +———-+ | COUNT(*) | +———-+ | 320 | +———-+ and counting the number of connection for each status can be done with: mysql…
Post: Time for Zero Administration effort at MySQL ?
January 8, 2012 By 4 Comments
… many Variables did MySQL get over years – it is pushing 400 these days even if we do not count variables/options which… natural step I would like to see taken is having MySQL to chose sensible values for them automatically, based on system… to say but hard to to especially in system like MySQL where a lot of things have been designed to be independent…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct …WHERE DepDelay>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim_date using (date_id) WHERE…
Post: MySQL Query Cache WhiteSpace and comments
March 20, 2008 By 9 Comments
…) mysql> select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (8.79 sec) mysql> select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where val like “%c%”; +———-+ | count…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
…: 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… affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql> select count(val) from fact where i
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…) where has_something=0; +————-+ | count(name) | +————-+ | 18001245 | +————-+ 1 row in set (35.96 sec) mysql> select count(name) from testr ignore key (has_something) where has_something=0; +————-+ | count…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from data where val in (-2,-3,-10,15,15,16) group by val ; +—–+——————–+ | val | count(distinct…
