… good scenario ? – query examining same amount of rows as they end up sending. In this case if I index the table I get the following record in the slow query log: # Time: 120911 17:18:05… 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
…index on the field that is used to join the two tables? A: The question in this case is how MySQL will execute the join…query is only selecting on vendor, will the index have any impact on the speed of the query? A: If you extend the index…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ Now let’s run some queries…
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‘s statistics or index…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… their top slow query according to pt-query-digest’s report. This query was …1s. When I ran the query myself (a two-table join with a WHERE condition…wanted to know more about how MySQL executes this query. So I used commands …reasonable and that fields were correctly indexed. With SHOW PROFILE, I saw …
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…query which performs differently between MySQL Server versions you should: Check Query Plan Run EXPLAIN to see if plans for the query…INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query…index lookups. Even if you look at 1% or rows or less full table scan may be faster. Avoid joins to large tables Joining…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…query with only one join, I’ve tested performance of the query with the join in both directions. Explain for query Q1.1: mysql> select straight_join…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona….Slow_launch_threadsblogpercona.commanual slow_launch_timeblogpercona.commanual Slow_queriesblogpercona.commanual slow_query_logblogpercona.commanual slow_query…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…MySQL 5.6. Test Case C – Join a small table with a large table with a WHERE clause on a non-indexed…query response time when you are joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join…
