…MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5…. # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types: …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it,…tables)? For performance, it’s hard to make a general rule, because there are different types of subqueries…, and making lookups to the `kind_types` table by primary key, the result was a query that…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement transactions (TPS… max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and load data and…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…$K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not … TokuDB performs if PK is (`id`,`hid`,`mid`). This also will affect select performance,…mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0 –num-threads=32 –rand-type…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… complains about MySQL interactive performance – serving…mysql –num-threads=1 –max-requests=0 –oltp-dist-type=uniform –max-time=180 –oltp-read-only –mysql-host=localhost –mysql-table-engine=innodb –mysql…PRIMARY 457.70432472 When we’re running sysbench OLTP on its own we have the primary key…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… this column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as rank, customer_id, total… response time. Refresh method performance comparison For demonstration purposes, I did the following: Created one view of each type. The view is… lines. Refreshed both views. Compared the refresh performance and query results. The view type does not usually* affect the time it takes…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…the performance and inner workings of one of the nicest features of the newer MySQL optimizer:…NULL, `role_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `role_id_note` (`role_id`,`note`) ) ENGINE…: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: role_id_note key: role_id_note key_len: …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…locks, at that point the ALTER simply blocks any type of queries to the table, we know that … You can see that no table open operation was performed and hence no wait. Does the fact that the…i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would just…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…type and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
…MySQL Query Cache so it once again is appealing to improve performance for MySQL Applications ? Make it Lockless Can we re-implement MySQL…instances by the same query typed in by different developers. …MySQL have very large number of queries doing updates through primary key and use primary key…
