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

[Dmitry Dulepov]

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. 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.

-- 
Dmitry Dulepov
TYPO3 expert / TYPO3 security team member Read more @
http://dmitry-dulepov.com/