… a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it,…equivalent solution. If you’re talking about using a temporary table to store an interim …WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join, can you explain…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… zero. If you know how InnoDB uses `PREC` please let me know. mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; +———-+—–+————-+——-+———+——+——+ | TABLE_ID… CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679… dumps of the dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints_parser generates mysql>LOAD DATA INFILE ‘/path/to/SYS…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… was explained in the original post, but seems to cause much confusion. Even though the read-only transactions optimization in MySQL 5… pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like… –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp…
Post: The write cache: Swap insanity tome III
April 19, 2013 By 5 Comments
… Cole explained this here and here. In summary, you need to interleave the allocation of memory for the MySQL process using the numactl… swapping issues but… I recently ended up in a situation where, after having done all that, a server was swapping episodically… S3. Why? The push to S3 was especially puzzling since MySQL was not involved. The server was running with nearly 7GB…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…: const rows: 185440 Extra: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type=6 \G *************************** 1…: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type between 3 and…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… pushdown. Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime… is always preferred over the ICP optimization: mysql> EXPLAIN SELECT role_id FROM cast_info WHERE role_id = 1 and note like ‘%Jaime…_id_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row in set (0.00 sec) I reported…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…appropriate where clause. This however would not tell you full story, especially now as MySQL …in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top…will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… Here is example from NNSEEK database: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 WHERE group_id=398157 AND parent_id=0… ref: const,const rows: 1 Extra: Using where 1 row in set (0.00 sec) Where group_id key is defined as KEY… index to be used to largest extent possible: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 force index(group_id) WHERE group_id…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…. There is however one extremely helpful metric which you can use – ratio between rows sent and rows analyzed. Lets look at…_sizes: 0 # InnoDB_trx_id: 12F03 use sbtest; SET timestamp=1347397784; select * from sbtest where pad=’abc’; The query in this case… a,sbtest b where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…mysql> explain select count(*) from trunc where i=4147483647; …| i | 4 | const | 81602 | Using where; Using index | +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ 1…
