… have star join with dimention tables being small it would not slow things down too much. On other hand join of few large tables, which is completely disk bound can be very slow. One of the reasons elevating this problem in MySQL is lack of advanced join…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… table however so you do not have all these problems with loosing data etc. Read more in my Why MySQL Could be slow with Large Tables article. 5. The n+1 pattern This probably should rather be called Not using Join. This is typical error. On other hand in MySQL you might be better of using…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system…; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type | table | type… actually join 2 tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…be used, which can be important for large, heavily written tables. Q: in trick #1 will “WHERE a …slow down your inserts and make primary key significantly fragmented. I also would note there are some MySQL…used to join the two tables? A: The question in this case is how MySQL will execute the join. If…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
…table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table…TABLES or SHOW TABLE STATUS on many a large tables…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… as compared to BKA algorithm of MySQL 5.6. Test Case C – Join a small table with a large table with a WHERE clause on… to be joined from supplier table (left operand). Test Case D – Join a large data set (>1M rows) from one table with a large table The SQL… hash join is slow in this case, because we have to refill the join buffer with rows from orders table many times, and hash join…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…large_files_supportblogpercona.commanual large_page_sizeblogpercona.commanual large…Slow_launch_threadsblogpercona.commanual slow_launch_timeblogpercona.commanual Slow_queriesblogpercona.commanual slow_query_logblogpercona.commanual slow…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the joins…of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL 5.5 and MySQL 5….in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is ‘…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… table however so you do > not have all these problems with loosing data etc. Read more in my > Why MySQL Could be slow with Large Tables article. Deciding whether or not to let the database… the indexes to join rows together, potentially preventing a table scan. The trick is to know what kind of JOIN to use so…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql…_value,40) var56 from var55 left join var56 on var55.variable_name=… | 8 | | QUERY_CACHE_SIZE | 0 | 1048576 | | SLOW_QUERY_LOG_FILE | /mnt/data/…values. I do not expect large impact here. table_open_cache is increased …
