…a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to view the recording…in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…table‘s index. Which one? A table can have many secondary indexes, but only in…how the primary index gets created it will have minimal `ID` among the indexes of the table. mysql> select * from SYS_INDEXES WHERE TABLE…are defined in CREATE TABLE statement internally they always go first in a record. …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…MySQL replication is asynchronous. What about semi-sync MySQL replication? It’s actually much worse than Galera. As I illustrated in a…table with a statement like this: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
…in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many…in the index record, and the remainder stored in overflow pages. COMPACT became the default after MySQL 5.0.3 and has a…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…in 587MB of raw data, mostly in one table (lineorder). Each of the 13 queries were executed serially in a…in set (0.00 sec) After running the query, see how many pages were read from disk versus how many page requests their were: mysql…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…: How many indexes is too many? A: There is a hard limit on amount of indexes you can have, which is 64 per table in recent MySQL versions… index in every table you create? Example: CREATE TABLE user_competition_entry user_id (INT), competition_id(INT); The table is only used to record a…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…mysql> load data infile ‘/tmp/words’ into table words (chars); Query OK, 6033 rows affected (0.01 sec) Records…how many physical cores are connected to my bus, and because I chose to create six hash “buckets” in the table. This allows MySQL…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite often want to know how…buffer is a special case. Unlike many of the …a_table join b_table on b.col1 = a.col1 join c_table on c.col2 = b.col2 You can see these un-indexed queries in…records 14178 Records are stored in tab->cache.buffer and last record in 14179 last record…
Post: Percona XtraDB Cluster - installation and setup webinar follow up Q&A
October 28, 2012 By 4 Comments
…A: Sure, see this post or webinar recording. Q: Fault tolerance: Can you recommend a three node cluster for production use? A…in mysql replication (asynchronous) for a given node, it’s not possible. However, a node in PXC can be a master or a…a lock using FLUSH TABLES…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…TABLE test add column c char(32) default ‘dummy_text’; Query OK, 2 rows affected (46.77 sec) Records…a connection accesses a table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL…
