… s4 are also lagging behind). s2> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ # s1 is behind s1> select count(*) from t; +———-+ | count(*) | +———-+ | 0 | +———-+ Can we still use… the number of records from the t table s1> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ Great! So again, using GTIDs avoids the tedious…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app_inventory cai…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… form. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly… the result was that it created a temporary table to count the movies per production year for each kind_id. It…
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 QPS test sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size…-updates=0 run POINT_SELECT + UPDATE QPS test sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $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…
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… = 3306 back_log = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache = 2048 max…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… number of follow and order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
…> 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 count(val2) from fact where i explain select count(val2…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…, PRIMARY KEY (`id`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact left…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
…: select count(col1) from table; If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count…’s another form for COUNT(): select count(distinct col1) from table; So when I see a query that just does COUNT(col1) I am… to them: select count(col1) from table; — COUNT_COL_OK Alas, there is a lot of misinformation on the web about COUNT(), so it…
