[MDEV-17825] EXPLAIN new index suggestion mode Created: 2018-11-25  Updated: 2022-04-14

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Sergei Golubchik Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: energy

Issue Links:
Relates
relates to MDEV-27146 extend EXPLAIN output to include "att... Open
relates to MDEV-27781 Expose syntax parse tree with name re... Open

 Description   

When optimizer considers different indexes for a query, for every index it needs to know

  • what columns are part of the index
  • the index cardinality
  • records_in_range — number of values within a specific constant range

Note that the first question doesn't need the index to be created. And the other two can be answered from EITS, so they don't need an index either.

So, the optimizer should, technically, be able to create an execution plan that uses an index, even if the index does not exist! This could be incorporated into a new explain mode, say EXPLAIN SUGGEST_INDEXES that will return what indexes (if created) would reduce the query execution cost and how.

 Comments   
Comment by Rick James [ 2021-12-13 ]

I have a prototype; it takes only a SELECT and generates suggestions for each table in a JOIN.

With more information, it could

  • Avoid including a TEXT in an index
  • Know what the PRIMARY KEY is – to avoid suggesting an index starting with that
  • Recognize when a LEFT JOIN is really a JOIN
  • Know what is in "*" (of SELECT *) when proposing a "covering" index
  • Know that a table has exactly 1 row or all rows have the same value in a tentative index column
    (and other things).
Comment by Sergei Golubchik [ 2021-12-14 ]

what kind of a prototype, in SQL?

Comment by Rick James [ 2021-12-14 ]

PHP with regexps. It would probably be much harder with just SQL. A parse tree would help a lot.

Generated at Thu Feb 08 08:39:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.