… Range Read With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition… depend on the number of rows that match the condition ‘key_column=x’. You can see that there are a lot of random primary… of the optimizer’s job to select the best query execution plan. I had noted a similar thing when benchmarking ICP, the…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…that very nasty “full join” select…are stored in MyISAM tables anyway) you can consider using –no-lock option with Percona Xtrabackup. Many other tools have similar…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
…that easy to implement it in the general sense as you can’t always track the future of individual row. Queries with temporary result set are especially complicated, for example: SELECT * FROM (SELECT COUNTRY…the groups are filtered out. There are many similar cases when decision of whenever row …
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… of hours of handling SELECT traffic, these same queries were responding nicely on the passive master, too. Is that all? “Buffer pool… workload on the server, that falls to 1.32G. Other tables are similar. The mixed workload with some SELECT queries mirrored is somewhere between… no database or table name — the second row in the table above. Those are a mixture of things, but it’s overwhelmingly…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… This is conceptually similar to a replication slave that is behind. Until …are complete refresh and incremental refresh. Â The view type/refresh method are interchangeable since a refresh method is selected…rows affected (7.01 sec) The second one is 365x faster because it examines only the rows that…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
…that makes this perfectly clear: CREATE TABLE .. AS SELECT (CTAS). The results of the SELECT portion of the statement are…) is called materializing the results. Views are similar to regular SQL statements. A view… the database rows must be examined The rows examined are related to the rows which have…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…that are concentrated against key values that lie in the leftmost and rightmost page and very few records concentrated against the key values that…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
…InnoDB/XtraDB. Tests show that PBXT’s performance is similar to InnoDB but, …rows that do not exist. This, in turn, means that PBXT transactions are not SERIALIZABLE. A result of this is that…rows are selected in this manner. I should also mention that the consequence of this is that SELECT …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… now contain only half as many rows. That will make the indexes less than… it on the master with INSERT..SELECT queries, it will propagate to …with tables that contain name-value pairs or something similar. The thought is that you can…that it’s not 100% optimal for. But if the costs of doing that are…
