… who asked the great questions. I answered as many as we had time for during the session, … try writing a SQL query optimizer and see how easy it is? ;-) Generative grammars like SQL can …because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses …
Post: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
… nearly every recovery case the same question arises: How many MySQL records were recovered and how many were lost. Until now there was no… difference. There are two ways to know how many records an InnoDB page stores. The index page has a header PAGE_N_RECS… fields have permitted values, etc. In the end it knows how many records were actually recovered. This statistics are printed along with…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… index identifier. We need it to find InnoDB pages which belong to the table’s index. Which one? A table can have many secondary indexes, but only in the primary index we can find all… be GEN_CLUST_INDEX. It doesn’t matter how the primary index gets created it will have minimal `ID` among the indexes of the…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single…: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many players might accomplish this achievement at the same time? Example… achievement = ‘killed_troll’”; } change your schema In Example 2, above, how above moving the ‘joined’ column to the users_groups table…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… There are numerous solutions for MySQL high availability. Many rely on MySQL’s asynchronous replication to …for replication. The buffer pool and adaptive hash index on the standby server will not have recently…`tail -f slow.log`, except that it knows how to follow the log stream across log rotation…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… as primary key. Q: How many indexes is too many? A: There is a hard limit on amount of indexes you can have, which is… point the gains from indexes you add will be less than performance loss because of having too many indexes. Q: Is there a difference between “`id` = 5″ and “`id` IN (5)” regarding indexes and performance? A…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… it is not index prefix. It is important to look at key_len column in explain plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes which are… some cases – Indexes are sorted (at least on the page boundary) so doing index range scan you typically get many index entries from the…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… and spindle speed. I need to know how many physical spindles are under the box, and how fast they are. I can also… big gains. Schema, query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries that run…. You cannot consider any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound together, and tweaking…
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… an index with range scans You would like to count how many books J.R. Hartley has ever written. You goto the index cards… covering index You are interested to know how many pages a certain book by J.R. Hartley has. You go over to the index… book and seeing how many pages it has. Conclusion If you imagine yourself doing these tasks, you can imagine how long they might…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand … how many pages are in buffer pool for given table (cnt), how many of them are dirty (dirty), and what is the percentage of index…/performance management. In many cases you would learn you need a certain fit to buffer pool for tables/indexes for reasonable performance…
