… ‘> order by total_price desc; ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view is ‘enabled’, the contents are created: mysql… demo.orders as o -> JOIN demo.customers as c USING (customer_id) -> JOIN demo.order_lines as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… before MySQL has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join algorithm…_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…_varchar ORDER BY state limit 10000, 5; Result time(mean): 0.0854793 3) select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY s.name limit 10000,5… why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY s…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… go to disk. (This distinction is abstracted away by the storage engine; the optimizer doesn’t know …. MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue … bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer… well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES from information_schema.tables t join information_schema.statistics i…_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES, s.index…, then you can rebuild the tablespace for your table by simply doing: mysql> alter table mytable ENGINE=Innodb; However, this blocks and…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…reduction of the set, all in parallel. All joins, aggregation and filtering is done at the …resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies…id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`,…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
…number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix-’, tb2.ID) AND -> tb2.gid = 1337 -> ) ORDER BY tb1.title…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…OF THE REPLICATED STATEMENTS Normally MySQL will not write into slow log any queries executed by the slave’s …all the rows from a table. Full_join means any of the joins didn’t use indexes. If a …c from sbtest where id between 501895 and 502895 order by c; or if InnoDB was not used: # Time: 071031…
