… InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer… used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt…=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… reduce the gap but I doubt it will be closed. Joins Joins are used to compose the complex object which was previously… 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… less full table scan may be faster. Avoid joins to large tables Joining of large data sets using nested loops is very…
Comment: Why MySQL could be slow with large tables ?
August 17, 2010 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… of index lookups which may be random.” Are the advanced join methods available now?
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
…, wrong use, NotABug. I’m thinking of the lack of sort-merge joins or intra-query parallelism, for example. It would be lovely… have these query plans usually try to use nested-loop joins whenever possible, because of things like the lower startup cost…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… to resolve query in optimal way – loose index scan, hash join, sort merge join are all the examples of such. For me it is…
Comment: Using Sphinx as MySQL data retrieval accelerator
August 22, 2008 By 25 Comments
Ed, No there are no bitmap indexes. Why they are not implemented ? Well there are a lot of features which are not (yes) – hash join, sort merge join, a lot of subqueries optimizations. It should come but over time.
Post: Watch out for Marketing benchmarks
August 23, 2006 By 7 Comments
… happens with many types of subqueries, cases when hash or sort merge join is needed and in many other advanced optimizer features. I…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/sandboxes/msb_5… small sorts for which allocation of 2M for sort buffer was very expensive. It can negatively impact some large sorts though shifting to sort merge…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…delayed_threadsblogpercona.commanual max_join_sizeblogpercona.commanual max_length_for_sort_datablogpercona.commanual …socketblogpercona.commanual sort_buffer_sizeblogpercona.commanual Sort_merge_passesblogpercona.commanual Sort_rangeblogpercona.commanual Sort_rowsblogpercona.commanual Sort_…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… is that nested-loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash joins, you’d have…, so you want to build your aggregate tables to avoid joins. Watch Data Types Does your ad ID look like “8a4dabde… data warehousing workload. The nested-loop joins are not all that fast on big joins; the query optimizer can sometimes pick…
