Re: Developing and Index Advisor for MySQL

Quoting Physical Database Design [1]:

"index statistics can be sufficiently estimated for the purposes of=20
physical design advisors from relatively straightforward calculations=20
based on the table and columns statistics of the underlying table that=20
the virtual index will be based on."

There are already tools like this for other RDBMS: Microsoft SQL Server=20
Database Tuning Advisor, Oracle Access Advisor and IBM DB2 Design=20
Advisor. I just want to create a tool that does the same thing for MySQL.

Furthermore I intend to analyze all the SQL queries, including SELECTs,=20
INSERTs, UPDATEs and DELETEs. Thus I want to find some index=20
configuration that is good enough for the entire workload. I do know=20
that workload can vary over time and the optimizations become outdated,=20
but this is an academic research and I am assuming a constant workload.=20
Adapt to dynamic workload changes is future work.

I plan to use some statistics such as how many times each query is=20
executedto make queries that run most often to perform better. So, if=20
there are more INSERTs and UPDATEs than SELECTs in a given table, I=20
intend to recommend less indexes for this table, and even suggest to=20
drop some existing indexes if needed.



[1] LIGHTSTONE S., TEOREY T., NADEAU T. Physical Database Design: The=20
Database Professional=E2=80=99s Guide to Exploiting Indexes, Views, Stora=
ge, and=20
More. San Francisco, CA, USA: Morgan Kaufmann Publishers Inc., 2007.=20
ISBN 9780080552316.



Em 12-11-2015 18:56, shawn l.green escreveu:
> 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=20
>> bachelor's degree in Computer Science.
>>
>> I have to do a final work for the course, and (I don't know why) I=20
>> 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=20
>> what indices can be created in the database, based on statistics and=20
>> query log. In other words, indexes that do not exist in the database=20
>> but can be created by the administrator in order to improve query=20
>> execution performance.
>>
>> I have never worked with MySQL internals before. I know a lot of C=20
>> and C++ programming, so I just need some help to understand MySQL=20
>> structure.
>>
>> My main inspirations for doing this work are [1] and [2] (actually,=20
>> they are the same work, but two different articles). One important=20
>> thing I have found in [1]:
>>
>>      Before making the decisions, the optimizer allows the developer=20
>> to override the information about physical design by using several=20
>> function =CC=B3hooks=E2=80=98. The hooks can be replaced at runtime wi=
th functions=20
>> that insert new stastistics information into the list of physical=20
>> design features. This makes the optimizer believe that the newly=20
>> inserted data regarding the what-if indexes and what-if tables are=20
>> present in the database. Then, the optimizer selects the execution=20
>> plans using the statistics from the what-if features.
>>
>> I guess that this is what I need. Some way to virtually create=20
>> indexes and then calling the MySQL optimizer or some sort of EXPLAIN=20
>> method to understand "what MySQL optimizer thinks about this index?=20
>> Would this index be used if it existed?".
>>
>> I am probably wrong about all I have said here, but I want to hear=20
>> 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=20
>> http://dl.acm.org/citation.cfm?id=3D1739131
>> [2] An automated, yet interactive and portable DB designer=20
>> http://dl.acm.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)=20
> or from the actual query statistics in the Slow query log (which can=20
> be converted to patterns by tools like mysqldumpslow) to look for=20
> 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.=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=20
> need) only hurts INSERT, UPDATE, and DELETE performance if you are=20
> trying to improve a query you only execute rarely.
>



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