Re: Developing and Index Advisor for MySQL

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 bache=
lor'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 cr=
eated by the administrator in order to improve query execution performanc=
e.
>
> 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, the=
y are the same work, but two different articles). One important thing I h=
ave found in [1]:
>
>      Before making the decisions, the optimizer allows the developer to=
 override the information about physical design by using several function=
 =CC=B3hooks=E2=80=98. The hooks can be replaced at runtime with function=
s that insert new stastistics information into the list of physical desig=
n features. This makes the optimizer believe that the newly inserted data=
 regarding the what-if indexes and what-if tables are present in the data=
base. Then, the optimizer selects the execution plans using the statistic=
s 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 un=
derstand "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
> http://eduardoweiland.info
>
> [1] PARINDA: an interactive physical designer for PostgreSQL http://dl.=
acm.org/citation.cfm?id=3D1739131
> [2] An automated, yet interactive and portable DB designer http://dl.ac=
m.org/citation.cfm?id=3D1807167.1807314
>
>

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

Other approaches (most of them are manual) have been to examine the=20
query patterns (available in digest form from the PERFORMANCE SCHEMA) or=20
from the actual query statistics in the Slow query log (which can be=20
converted to patterns by tools like mysqldumpslow) to look for patterns=20
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.=20
In many cases, though, it was because the query used a dependent=20
subquery which simply had to search a lot of row combinations.

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

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

Become certified in MySQL! Visit https://www.mysql.com/certification/=20
for details.

--=20
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals