…`) where `id` is sequential, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects by…`,`hid`,`mid`). This also will affect select performance, so we will need to measure that also. And, if you want to repeat… socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are all defaults, as I understand from documentation TokuDB…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…where probably only recently read records will be cached, resulting in more disc reads. 2. If you need to delete records from…be quicker on a partitioned table, as Mysql can fire off one thread per …Selects‘ and Inserts, its also about what you do with the table. Removing partitions is easier and…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test) > select var55….opinion. MySQL 5.6 adds a lot more optimizer switches which you can play with: mysql [localhost] {msandbox} (test) > select * from var55 where variable_name…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that …session1 > select * from test where id=1; +—-+——+————+ | id | x | c | +—-+——+————+ | 1 | foo | dummy_text | +—-+——+————+ 1 row in set (0.00 sec) And you …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…INDEX_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX_NAME != ‘PRIMARY’ and all_indexes….end of our SELECT. I can now select my droppable (unused) indexes from this view: mysql> select * from droppable_indexes; +————–+—————+—————————–+ | table…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…; select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the actual… more data about the underlying tables involved and the query execution plan used by MySQL. The end result might be that you… could be to instead of selecting every column from all the tables involved in the query, probably selecting only the needed columns which…
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 | key… unsigned column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… have created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row … and when it can be found and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL …
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… query: mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name=’Music’; and let’s….sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row in set (0.00 sec) mysql> exit And now if you look…
