… but because the access type to the tables is “const” MySQL does not count it as access to two tables. In… or no” answer but rather helps to understand how much optimization is possible. For example I might have query which uses some index… adding combined index. So what is the easy way to see if query is worth optimizing ? – see how many rows query sends after…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… that MySQL can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index condition pushdown. Q: Is it always the case that no second-star index exists for a query with…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice…. There is no workaround, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using index“. So, I…-party, independent and technical opinion about the new features of MySQL query planner? Are you not yet familiar with terms like MRR…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index… it picks smaller index. The query takes 290ms which is exactly what we’ve seen before. What if we leave MySQL no choice but only to use combined index: mysql…
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
…. We can gather just about every bit of information about queries that mysql-proxy can, making this a viable way to monitor…% of queries that say No_index_used? If we increase –limit a bit, we can see # 0% No_good_index_used # 100% No_index_used # Query_time distribution … snip … show databases\G I did not know that SHOW DATABASES sets the “no index used…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… to the MySQL queries would make them even slower, I’m sure. Indeed, the result set of 20 rows from SphinxQL queries is… not be found? No, only the documents included in the Sphinx Search indexes will be returned by Sphinx Search queries. Mike W. also asked: Q: What about MemSQL and indexes. Have you…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {… tries to enable it. sql_mode has NO_ENGINE_SUBSTITUTION value by default which is …index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… behind index merge is that for certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes… get you back in the house, but now you have no door, and your cats might get out. Or your neighbors…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql…query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries…. At the moment there is no other technique that approaches the …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… are the same). Of course UNIQUE index optimizations can no longer be applied either. A: This…query is only selecting on vendor, will the index have any impact on the speed of the query? A: If you extend the index…Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid …
