…constrains. #859078 (Alexey Kopytov). After adding an index using Fast Index Creation, statistics for that index provided by InnoDB were left in a …851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE query with compound indexes). #832528 (Alexey Kopytov). Added a …
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
…expect to deliver huge wins. If MySQL is actually badly configured, you …my.cnf file and see something wrong. Usually, though, the consultant needs …schema design (table design, data types, indexing, etc) The data itself All of…changing the way that the application uses the database. These changes can …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… other ranges do not. Using same access type in EXPLAIN makes it very confusing. I also was wrong about bug in key… one index range scan was initiated. Handler_read_next=42250 means 42250 rows were analyzed during this scan. Basically MySQL started scanning Index… | Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql> explain…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
…do not expose itself instantly – you may notice wrong query results days after crash. Sometimes corrupted …penalty. If using Innodb you also might with to use innodb-safe-binlog option in MySQL 4.1 … using LOAD INDEX INTO CACHE statement, for other storage engines it can be done by issuing large index…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…mysql> pager Default pager wasn’t set, using stdout. Or \n: mysql… indexes. Discarding the result is possible with pager: mysql> pager…mysql> pager md5sum PAGER set to ‘md5sum’ # Original query mysql> SELECT … 32a1894d773c9b85172969c659175d2d – 1 row in set (0.40 sec) # Rewritten query – wrong mysql…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
…’ Data records: 3 – Fixing index 1 Data records: 6 And then, back in mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select * from test…’ Data records: 3 – Fixing index 1 myisamchk: error: Couldn’t fix table with quick recovery: Found wrong number of deleted records myisamchk…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL… the faster plan may be wrong plan from optimizer standpoint and updating stats may cause old MySQL Version to get slower plan…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… don’t see anything wrong there. So at this point, it looks like MySQL is probably a good… consider any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound together,… If it’s not possible to use a patched binary, we can use MySQL Proxy, packet sniffing, or …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… for index prefixes for index stats and these rarely change. There is no histograms or any other skew metrics etc. MySQL optimizer also uses… is need to bother – for bad plans use FORCE INDEX or change the query and report MySQL Optimizer Bug 
But now lets see… as with MyISAM but instead we get very wrong cardinality which shows us index prefix is unique (245900 is estimate for the…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…if Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to …but I guess not everyone knows how much wrong it could be. If number of rows is…index based filtering applied. The other reason for difference is join_buffer which reduces number of passes MySQL…
