…, $level) { $result = mysql_query(“SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=” . $parent); echo “”; while ($row = mysql_fetch…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… great questions. I answered as many as we had time for … primary key solution for random selection only work when the …every solution for randomly choosing rows involves some compromise, either of…count the movies per production year for each kind_id. It was more efficient in this case to force MySQL…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app_inventory cai inner…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…that are as close as possible… with 1M rows each (about …select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…fill TokuDB tables with 1bln of rows on this SSD, and projected… to 60-sec checkpoint interval, as TokuDB does time-based checkpoints…. selectivity is not good for fast inserts, but it is suitable for range selects…table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…( [0] => SELECT word AS `word`,md5(word) AS `md5(word)`,md5(reverse(word)) AS `md5(reverse(word))`,COUNT(*) AS `count(*)` FROM words2 AS `… degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ 1 row …
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… SELECT to see the actual count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where type=6 \G *************************** 1. row *************************** count(*): …an estimation.” This seems to be a good fix as sampling more of the intermediate pages is going …
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…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> SELECT…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…_trx_id: 12F14 SET timestamp=1347398285; select * from sbtest where pad=’abc’; Rows_examined=0 same as Rows_sent meaning this query is optimized… perform but as only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined…_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million, while we…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… ranfom data. The only row we really need is with 2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | type… | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set, 1 warning (0.04 sec) | Handler_read_next | 1305819030 | As you…
