Developing and Index Advisor for MySQL

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 indi=
ces can be created in the database, based on statistics and query log. In o=
ther 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 ar=
e the same work, but two different articles). One important thing I have fo=
und in [1]:

    Before making the decisions, the optimizer allows the developer to over=
ride the information about physical design by using several function =CC=B3=
hooks=E2=80=98. The hooks can be replaced at runtime with functions that in=
sert new stastistics information into the list of physical design features.=
 This makes the optimizer believe that the newly inserted data regarding th=
e 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-i=
f 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 understa=
nd "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.acm.or=
g/citation.cfm?id=3D1807167.1807314


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