… 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 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
…/N) where, S is the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… 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
… the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions MariaDB in many places, so you can learn more about where… 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
… tpcc-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: 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… view, as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview_id= flexviews.get_id(‘demo’,'dashboard_customer…. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL\G *************************** 1… where the REFERENCED_* columns are non-null. From that information, we can generate an exclusion-join query for each foreign key relationship: mysql…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… perfect scalability. So there are workloads where the problem is solved and when there are where a lot of work need to…_quick,sub_select,do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command…_quick,sub_select,do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 25 Comments
… a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there’s no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan. SELECT * FROM A, B WHERE A.ID = B.ID; SELECT * FROM A JOIN B…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… the query in the processlist, and you can track down where it’s coming from in the source code. The problem… exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb….trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1…
Post: Product to try: MySQL/MariaDB-Galera 0.8
June 13, 2011 By 34 Comments
… excited that this solution is available for MySQL users as Free / Open Source Software. “Where is the catch?”, you may ask. Yes… three node cluster is much more complex than an average MySQL master-slave setup. Potential performance penalty: Although to have performance… invite you to join me in testing MySQL/Galera 0.8, binaries are available from Launchpad. The downside of MySQL/Galera is that…
