Bug #2240 New Index Type - "Super Index" (like an ADABAS superdescriptor)
Submitted: 30 Dec 2003 14:44 Modified: 28 Nov 2005 9:17
Reporter: Jon Koerber Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:4.0.16 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[30 Dec 2003 14:44] Jon Koerber
Description:
I would like to suggest the addition of a new Index type to MySQL.  I am an ADABAS DBA and have 
always enjoyed the concept of the Adabas "Superdescriptor" indexes (as well as sub-descriptors).  
Basically these indexes combine several fields (or partial fields) together to make a single index.  
They are really usefull for sorting and searching complex data.  They differ from the multi-column 
indexes in MySQL in that the descriptor can be accessed like a field and can be used directly in a 
WHERE clause.  I would really like to see a "Super Index" (and sub) Index type added to MySQL.

Here is an example of a use for this new Index in which MySQL does not have the functionality I 
need:
   I have a help system in which each help topic is assigned a section code, a chapter code, and a 
sub-chapter code.  For example "005" "010" "025" for the Introduction section.  The codes allow 
me to sort the help topics to 
produce an automated table of contents for the help file.    What I would like to be able to do is 
combine these fields into a single Index like "005010025" in order to do SELECTS that can read 
forward and backward from the current topic using the ORDER BY clause.  Currently, I am forced to 
combine the fields into a single field in MySQL in order to accomplish this, If I also wanted to 
search on these fields arranged in the reverse order (from sub-chapter, chapter, to section) I would 
have to 
duplicate and reverse the field ("025010005") in the MySQL table instead of simply creating a new 
superdescriptor with the fields reversed in it.

There are a lot of other uses for an Index type like this in MySQL.

Thank you for the consideration.

Jon C. Koerber
President
ClassJuggler.com

How to repeat:
See Description

Suggested fix:
See Description
[28 Nov 2005 9:17] Valeriy Kravchuk
Thank you for a feature request. I am not sure that this "superdecriptor" type of index is theoretically sound for the relational database, but it can be emulated by so called "expression-based indexes", and if you will be abler to create index on CONCAT(col1, col2), then it may help to solve the problems you mentioned. 

I believe, expression-based indexes will be supported some day.