June 19, 2013

Post: How to Identify Bad Queries in MySQL

January 10, 2011 By 16 Comments

… the variance-to-mean ratio, the Apdex score, and the explain sparkline to see whether the query is likely to be possible to optimize. It finds queries or groups of queries that add too much load to… Law to decide which of those are candidates for improvement. (The V/M ratio, Apdex score, and the explain sparkline are useful here…

Filed Under: Insight for Developers, MySQL

Post: 3 ways MySQL uses indexes

September 12, 2009 By 28 Comments

…. It is important to look at key_len column in explain plan to see how many index parts are actually used for row lookup… on the optimizer. This just describes how MySQL uses single index – there are more complex rules of how indexes will be used if… using ORDER BY without and where clauses on the table. In such case you would see “Index” type in explain which correspond to…

Filed Under: Insight for Developers Tagged With:

Post: Is it query which needs to be optimized ?

March 6, 2007 By 9 Comments

… our customers regarding query optimization… which had one thing in common – It is not query which needed to be optimized. Way too frequently… done using Index or it can be slow on large data sets if external filesort is used so make sure to check explain for this one. So frequently I see dynamic query generation when some query variants fall back to use…

Filed Under: Insight for Developers Tagged With:

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

… used for filtering) work, when they are a good idea to use and what to do when they are not, but you still want to… confusing, but let me explain. Query returned 20 rows because unless you specify an explicit LIMIT, it defaults to 20. Total says… is a neat performance optimization for highly selective attributes, this is certainly not something you would want to use for every attribute. There…

Filed Under: Insight for Developers, MySQL Tagged With: , ,

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

… fits in memory to a large table with no WHERE clause The SQL used for this test together with its EXPLAIN output as… to a large table with a selective WHERE clause on an indexed column The SQL used for this test together with its EXPLAIN…=8 it always uses BKAH (a variant of BKA). Optimizer does not try to check which algorithm is the best one to use, it just…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… why the EXPLAIN plan for a given SELECT would show N possible index choices but only one index actually used. To some extent, MySQL 5.0 and later changed this situation with the introduction of the “index merge” optimizer plan… Finally, we can use index hints. Index hints are exactly what they sound like – “help” for the optimizer to favor or disfavor a…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…optimization. Back to the output of mysqladmin. When I send the report to the client, it typically looks something like this. First, I explain… the status and the variables, and explain to the client what I can deduce… serious analysis it’s basically mandatory to use a server that has the Percona…

Filed Under: Insight for Developers Tagged With:

Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT

September 18, 2007 By 10 Comments

…); Using where; Using filesort 1 row in set (0.00 sec) As you can see MySQL 5.1.21 uses sort_merge to access… doing merge sort to get resulted rows in sorted order. Once this is implemented similar approach could be used for optimizing ORDER BY… order by) optimally – in creates result set for the union fully and when only takes 10 rows from it: mysql> explain (select…

Filed Under: Insight for Developers Tagged With: , ,

Post: Extended EXPLAIN

July 24, 2006 By 9 Comments

… well as what other notes optimizer may wish to tell us. It is best seen by example: mysql> explain extended select * from sbtest… optimizer could not however catch id>5 and id>6 can be converted just to id>6. Lets see another example: mysql> explain… information in the message. We can see query is using , it is converted to using . And there is some in being done. Unfortnuately…

Filed Under: Insight for Developers Tagged With: ,

Post: Full table scan vs full index scan performance

November 23, 2012 By 15 Comments

… (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according to the documentation…: NULL). What do we get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,
« Older Posts
Newer Posts »