…; mysql> change master to master_port=10002; mysql> start slave; # And then check the number of records from the t table s1> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ Great…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
… KEY (`id`)) —————————————————————————————————— $mysql=mysql_connect(’127.0.0.1′,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql_query(“SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… form. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly… to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
…, ACTIVE 3 sec fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(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…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id`. However it… if PK is (`id`,`hid`,`mid`). This also will affect select performance, so we will need to measure that also. And…=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max…
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> SELECT…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… row we really need is with 2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | type | possible_keys | key… look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set, 1 warning…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… with -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 row in set (0.00 sec) mysql> exit And now if you…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in set (0.02 sec) Each year has tens of millions of flights: mysql> select count(*) from…
