… is Hash Joins. Hash Join As I have told 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… size 6M (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6 which…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_… 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer… well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… complete in order to complete locking… nasty “full join” select to complete…on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host | db …
Comment: Introducing new type of benchmark
February 25, 2012 By 16 Comments
Twirrim, Our tpcc-mysql has JOINs, there are queries like “SELECT c_discount, c_last, c_…_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…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…, count(*) as total_lines from orders o join customers c using(customer_id) join order_lines ol using(order_id) group by customer_id, customer… functions. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; Query…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…. Let’s start with the MySQL query optimizer. The optimizer tries to choose the best join order based on its cost metric; it… dimension tables. MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… leading table. If ORDER BY is going by field from the table which is not first in the join order index can’t be used. Sometimes it means breaking normalization and duplicating column(s) you’re going to use in ORDER… if it is possible to use index to do ORDER BY with JOIN MySQL still will not be able to use it as…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT … ‘fk_lineitems’, TABLE_SCHEMA = ‘ecommerce’, TABLE_NAME = ‘LineItems’, COLUMN_NAME = ‘order_id’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, …
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
… I did – forced it to use key, forced a different join order, it did not want to use tb1.vid index for… number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…, let’s check if that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…
