…mysql> select * from all_indexes\G … *************************** 33. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: rental INDEX_NAME: rental_date NON_UNIQUE: 0 COLUMN_CNT: 3 COLUMN…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows…used then, after the selected rows are read from table1, the values of indexed columns that will be used…performance. As I told you above, when table t1 would be joined to table t2, then selected rows …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable… query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys. SELECT * FROM tbl WHERE…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…columns. And not only will every index be a little narrower, the table will now contain only half as many rows…. If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and …save that work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, …
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
… see if your rows will benefit from compression would be to read Peter Zaitsev’s blog post and execute: SELECT AVG(LENGTH… length columns (BLOB, VARCHAR, TEXT) in the index record, and the remainder stored in overflow pages. COMPACT became the default after MySQL…? As the fine MySQL manual states: This setting may still be useful for tables with many long BLOB, VARCHAR or TEXT columns, because…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…rows column in the EXPLAIN output of the query was totally off. The actual number of rows…SELECT to see the actual count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where type=6 \G *************************** 1. row…seems to be a good fix as sampling more of the …
