… it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once you have spotted the query which performs differently between MySQL Server versions… hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it only can do nested loops method which requires a lot of index lookups which may be random. Here is good example…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… key: PRIMARY key_len: 4 ref: example_db.t.tag_prd_id rows: 1 Extra: Using index *************************** 3. row *************************** id: 1 select…: Using where; Using index In this case MySQL does not print ref at all, because there is no join, however you can notice… comes down to removing range condition from join one way or another. For our example query this could mean introducing additional DATE…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… of MySQL 5.6. Test Case C – Join a small table with a large table with a WHERE clause on a non-indexed… join algorithm used by setting appropriate values of “join_cache_level”. For example in my test I forced the optimizer to use hash join by setting join_cache_level=4…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… many point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations like for example if columns… 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…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash joins, you… the ranges of values for various attributes, for example; or the presence or absence of values. … can we improve here? Especially assuming that there are indexes other than the primary key, we can …
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…’m speaking about comes from the issue with MySQL optimizer stopping using further index key parts as soon as there is a…) of the index will be used which can be seriously affect performance. Of course in this example you can use index (B,A… some pre-generated table and use the join to retrieve the rows from original table: mysql> show create table dl \G *************************** 1…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… be some non-index based filtering applied. The other reason for difference is join_buffer which reduces number of passes MySQL had to… restricts how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10… well known – for example BLOB fields can’t work with in memory temporary table. The other thing – since MySQL 4.1 sorting…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…| | wsrep_local_index | 1 | | wsrep_ready | ON | only wsrep_local_index…mysql server: [root@percona1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL…one component. For example if you have…d56d-11e1-0800-f7c4916d8330, } joined { 7e6d285b-d56d-11e1-0800-2491595e99bb, } left …
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… you join together you may have serious issues because such tables do not get any indexes. Let me now show couple of examples…: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select * from user_counts; +———+——+ | user_id… a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt…
