… instead of expected performance improvement you see your performance being worse. What should you do ? First if you followed MySQL upgrade best practices… (for example with MySQL Sandbox) can especially be helpful. Once you have spotted the query which performs differently between MySQL Server versions you… to the old one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… (BNL) Join of MySQL 5.5.24, Batched Key Access (BKA) Join of MySQL 5.6.5 and Block Nested Loop Hash (BNLH) Join of… test cases and then see how the joins perform for each test case. Test Case A – Join a small table that fits in… where clause that reduces the size of the joining data set. And hash join performs even badly and takes 7x more time for…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… is further extended to improve join performance. As I told you above, when table t1 would be joined to table t2, then selected… 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… for the best possible performance gain. This is not entirely visible in the manual either for MariaDB or MySQL, but you need…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. In practice you… show close performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…_table_alias: o mview_join_condition: NULL mview_join_order: 999 *************************** 2. row… in response time. Refresh method performance comparison For demonstration purposes, I … <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer_…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… to be joined to get query result. If you’re executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL… very expensive. To get better performance you can “Help” MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual,….commanual join_buffer_…Performance_schema_mutex_classes_lostblogpercona.commanual Performance_schema_mutex_instances_lostblogpercona.commanual Performance_schema_rwlock_classes_lostblogpercona.commanual Performance_schema_rwlock_instances_lostblogpercona.commanual Performance…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot…. The other reason for difference is join_buffer which reduces number of passes MySQL had to perform and so number of row combinations… line So it is impossible to tell at which stage MySQL actually perform the sort or creates temporary table and so how…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… a lot of performance gains before I move to such unusual setup. Q: Table1 has a primary key. Table2 joins to table1… is used to join the two tables? A: The question in this case is how MySQL will execute the join. If it will… “`id` = 5″ and “`id` IN (5)” regarding indexes and performance? A: Recent MySQL versions are smart enough to convert id IN (5…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search… Timour explanation somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using… important than backward compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer…
