… my MySQL Indexing: Best Practices Webinar (both recording…indexes. Q: Is there a difference between “`id` = 5″ and “`id` IN (5)” regarding indexes and performance? A: Recent MySQL…indexes, if not you will need to add indexes manually. Q: What are some methods to overcome vastly differing cardinality…
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… rows off 3-10 times. It gets even worse if cardinality is used. In this case if data distribution is skewed… non-index based filtering applied. The other reason for difference is join_buffer which reduces number of passes MySQL had to perform and…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… need to take care of or which may seriously affect MySQL Performance. MyISAM Corruption – If you’re writting to MyISAM tables there… extreme load. Innodb statistics – Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table access…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…instead of expected performance improvement you see your performance being worse. What should you do ? First if you followed MySQL upgrade best …SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL Versions…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
…optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed …
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full table scan is performed and in this case turns to be faster than scanning 1/5th of the table using index. Also Full…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this…)) So by rewriting query using subqueries we actually improved it performance 100 times. So subqueries are not always slowing things down…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…the index will be used which can be seriously affect performance. Of course in this example you can use index …thousands of groups at the same time ? Performing such query MySQL has to build (and do lookups) for…how it is. I also tried to increase cardinality by having all rows to have different group…
Comment: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… cardinality, index traversing speed depends on the index cardinality. Note in this case it is not pure index scan (“Using index… if we would get random disk IO performance difference can be as large as 100 …should be same as count(*) and so covering index could be used while MySQL is not smart enough…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups. This is however not performance…. Another thing you may find unusual is – the key cardinality affects performance even when this key is not directly used for lookup…
