[TYPO3-core] RFC #3107: Duplicate database index in indexed search

[Martin Kutschker]

Dmitry Dulepov schrieb:
> Hi!
> 
> Martin Kutschker wrote:
>> I don't think this is a dupe. IMHO the field order in an index definition is import.
>>
>> See http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html:
>>
>> "If the table has a multiple-column index, any leftmost prefix of the index can be used by the
>> optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you
>> have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
>>
>> MySQL cannot use an index if the columns do not form a leftmost prefix of the index. "
> 
> It is not exactly like that.

Well I quoted the docs :)

> Position is important for WHERE and ORDER
> BY: columns used in ORDER BY must go after WHERE columns and in the
> right order. Order of WHERE columns is not important.
> 
> "High Performance MySQL" books gives better info on this. It does not
> make sense to have columns in the reverse order unless they are used in
> ORDER BY.

I haven't enough knowledge of indexed search to tell if this index is necessary or not. So neither
+1 nor -1 for this from me. Judging this is IMHO not trivial as you would have to understand the
extension and know all the queries it executes.

Masi