…“MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If… key solution for random selection only work when …, but your random number generator picks values …solution for randomly choosing rows involves some compromise, either…to is this example: WHERE (k1.keyword, k2.keyword, k3.keyword) =…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… a large number of questions… with 1M rows each (about 2…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: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…number of sleeping connections can be done with: mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND=’Sleep’; +———-+ | COUNT(*) | +———-+ | 320 | +———-+ and counting the number…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… following query in particular that had this surprising outcome: mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’; On… time to execute: mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’\G 266 rows in set (1… ref: const rows: 10259274 Extra: Using index condition 1 row in set (0.00 sec) With ICP, the actual number of rows read at…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…SELECT * FROM table WHERE…numbers…SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_select,mysql_execute_command,mysql…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… sequence number 380166807992 1 row in set (0.41 sec) 1 row in set (1 min 0.00 sec) Log sequence number 380170274979 1 row… following query: mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name=’Music… 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…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
… As with the previous options we can get the number of connections and failed connections made by a …the information about a particular user: mysql> select * from user_statistics where user=’root’\G *************************** 1. row *************************** USER: root TOTAL_CONNECTIONS: 25…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…: 0 NUMBER_PAGES_READ: 39009 * NUMBER_PAGES_READ: 38960 NUMBER_PAGES_CREATED: 0 * NUMBER_PAGES_CREATED: 0 NUMBER_PAGES_WRITTEN: 1 * NUMBER_PAGES_WRITTEN: …date.D_DateKey rows: 2837 Extra: Using where 2 rows in set (0.00 sec) mysql> select * from information_schema.global_status where variable_name …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… 1 row in set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select…any two numbers sum to zero: mysql> select a.val, b.val from ex2 a,ex2 b where a.val +…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… a degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ 1 row in … way parallelism: select min(result) == 0 is_prime from ( select mod({$NUMBER_TO_FACTOR}/prime_number) from dataset where id between…
