MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:shawn Date:November 12 2015 8:56pm
Subject:Re: Developing and Index Advisor for MySQL
View as plain text  
Hello Eduardo,

On 11/5/2015 4:58 AM, Eduardo Weiland wrote:
> Hello there,
> I am new here in the list. I am from Brazil and I am finishing my bachelor's degree
> in Computer Science.
> I have to do a final work for the course, and (I don't know why) I came out with the
> idea of developing an index advisor for MySQL.
> Just to be clear, by index advisor I mean some tool that suggests what indices can be
> created in the database, based on statistics and query log. In other words, indexes that
> do not exist in the database but can be created by the administrator in order to improve
> query execution performance.
> I have never worked with MySQL internals before. I know a lot of C and C++
> programming, so I just need some help to understand MySQL structure.
> My main inspirations for doing this work are [1] and [2] (actually, they are the same
> work, but two different articles). One important thing I have found in [1]:
>      Before making the decisions, the optimizer allows the developer to override the
> information about physical design by using several function ╠│hooks‘. The hooks can
> be replaced at runtime with functions that insert new stastistics information into the
> list of physical design features. This makes the optimizer believe that the newly inserted
> data regarding the what-if indexes and what-if tables are present in the database. Then,
> the optimizer selects the execution plans using the statistics from the what-if features.
> I guess that this is what I need. Some way to virtually create indexes and then
> calling the MySQL optimizer or some sort of EXPLAIN method to understand "what MySQL
> optimizer thinks about this index? Would this index be used if it existed?".
> I am probably wrong about all I have said here, but I want to hear what you think
> about this.
> I am very grateful for all your help.
> Att.
> Eduardo Weiland
> [1] PARINDA: an interactive physical designer for PostgreSQL
> [2] An automated, yet interactive and portable DB designer

The drawback to your approach is that you won't have the cardinality 
estimates for your index trials until you actually test those tuple 
combinations for uniqueness.  It is the cardinality that the optimizer 
bases its estimates on.

Other approaches (most of them are manual) have been to examine the 
query patterns (available in digest form from the PERFORMANCE SCHEMA) or 
from the actual query statistics in the Slow query log (which can be 
converted to patterns by tools like mysqldumpslow) to look for patterns 
that are both
a) frequently used
b) examining many more rows than they need to return

Quite often, they can be improved by adding indexes to certain tables. 
In many cases, though, it was because the query used a dependent 
subquery which simply had to search a lot of row combinations.

So, it is easy to suggest indexes that would solve certain queries 
quickly but over-indexing a table (creating more indexes than you need) 
only hurts INSERT, UPDATE, and DELETE performance if you are trying to 
improve a query you only execute rarely.

Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit 
for details.
Developing and Index Advisor for MySQLEduardo Weiland5 Nov
  • Re: Developing and Index Advisor for MySQLshawn l.green12 Nov
    • Re: Developing and Index Advisor for MySQLEduardo Weiland13 Nov
      • Re: Developing and Index Advisor for MySQLshawn l.green13 Nov