… rows, index lookup would be performed on t2. Here is where Key-ordered scan comes in. With BKA turned on, the… one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join algorithm only works with equi-joins…_shippriority from customer, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o_custkey and l…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and…_custkey i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… MariaDB in many places, so you can learn more about where its features might be helpful to you. So what’s… log enabled Faster queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key…
Comment: Introducing new type of benchmark
February 25, 2012 By 16 Comments
…-mysql has JOINs, there are queries like “SELECT c_discount, c_last, c_credit, w_tax FROM customer, warehouse WHERE w_id…, o_entry_d, COALESCE(o_carrier_id,0) FROM orders WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = (SELECT MAX(o_id) FROM orders WHERE o_w_id = ? AND o_d_id = ? AND o_c…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… matching range from some pre-generated table and use the join to retrieve the rows from original table: mysql> show create…: PRIMARY key_len: 3 ref: NULL rows: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table…: PRIMARY key_len: 3 ref: NULL rows: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…_id = t.tag_prd_id JOIN items_ordered i ON i.itm_prd_id = p.prd_id WHERE t.tag_name = ‘lcd… being used by the last join. How should we understand this then? Database reads all ordered items where tag is ‘lcd’, which…_id = t.tag_prd_id JOIN items_ordered i ON i.itm_prd_id = p.prd_id WHERE t.tag_name = ‘lcd…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… from cities_varchar WHERE state=’Minnesota’ limit 10000,5; Result time(mean): 0.085637 3) Results for INT + join: select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) WHERE s.name… no join: mysql> EXPLAIN select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) WHERE…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… (convert.php) supports basic queries which use select/group by/join/where. You may not use sub-queries or any non-deterministic… in the data dictionary: mysql> select * from flexviews.mview_table where mview_id= flexviews.get_id(‘demo’,'dashboard_customer_sales’)\G… this one, this creates a ranking function automatically. In cases where this is not useful, simply ignore this column. It is…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… fact where i select count(*) from fact left join dim on val=id where i select i,pad from fact left join dim on val=id where i select i,pad from ( select i,val from fact where i
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
… was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix… that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix…
