…port=10002; mysql> start slave; # And then check the number of records from the t table s1> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ Great…’Last_Executed_GTID’ column would have been useful. In our case we can check that 3ec18c45-c3ae-11e2-b986-0800272864ba is…
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… table to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… are two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed… case #2 and focused only on case #1. Which is the perfect case for read-only transaction optimization in MySQL 5.6, because all SELECT…: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… intensive. So what are results in this case: InnoDB gradually declines as data growth (which is… is low selectivity is not good for fast inserts, but it is suitable for range selects by `id… –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… in set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1; +———–+ | sum… | 1 | +—–+———-+ 9 rows in set (0.00 sec) mysql> select sum(`count(*)`) from ex2; +—————–+ | sum(`count(*)`) | +—————–+ | 73027220 | +—————–+ 1 row in set (0.00 sec…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set, 1 … throwing the error is not good idea in most cases, yet MySQL continues to be very loose by default. Consider …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… this case we actually join 2 tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of “real” access to the…_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… consider is string comparison in MySQL is case insensitive by default while hash comparison will be done case sensitive unless you lowercase string… competition_id, nothing more. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… Table Cache Related Allocations There are cases when MySQL will allocate a lot of …might want to look at Prepared_stmt_count to see how many prepared statements …temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1…
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 select count…
