… complete answers: Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it… I did not use the STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index-scan of 7 rows… in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the…
Post: Percona Server 5.1.69-14.7 now available: A drop in replacement for MySQL
June 10, 2013 By 4 Comments
… account owner could perform brute-force password guessing attack on other…in INFORMATION_SCHEMA (CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS, THREAD_STATISTICS,…#1003776. XtraDB changed page tracking used to hold the log …after an INNODB_CHANGED_PAGES query started returning data to indicate…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… query_cache_type=OFF performance_schema=0 warmup To warmup server and load data and indices to the buffer pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering for… of rows to display only few. If we force index as in second query explain will look scary with estimated million of rows… badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… to run this query mysql first will try to find each row where col1 is A or B using index. Then its going…. MySQL can chose only one of them to execute the query – use index to find rows. This is sensible strategy if there is… scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according…). What do we get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first…: 300584 Extra: Using index The optimizer chooses the full index scan. It is a good choice because the index now covers the query, which means…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use … need to use FORCE INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index changes …
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…’s check execution time with and without index mysql> select count(name) from testr force key (has_something) where has_something=0… see with index the time is by 3.5 times slower. Good that mysql in this case choose do not use index mysql… (10.62 sec) query with index is still 2 times slower. and this time mysql is going to use index in execution plan: mysql…
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
…try to force it ? mysql> explain select * from article force index (PRIMARY) where article_…using “”. You may ask why MySQL can’t use index in this case, simply by converting number to the string and performing index lookup ? This can’t be done as it would result in wrong result for some queries…
