4

Is it right to have more than one INDEX key for the same column in MySQL database?

for example, id field indexed twice with different Keyname while phpmyadmin gives me a warning message:

More than one INDEX key was created for column id

I would like to know if that is ok, and if there are any effects or side-effect on my script or the server using this method?

I use this method for grouping columns for each index.

Improve this question
1
  • 1
    Why do you want to do that in the first place? Commented May 27, 2011 at 2:11

2 Answers 2

Reset to default
10

Indexing a single column twice is useless, slows down inserts and updates because now you have to (uselessly) maintain two indexes, and probably confuses the optimizer (if it doesn't actually break something). On the other hand, it's fine (and often useful) to have a column indexed alone and then also as part of one or more compound keys.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

could I ask you where you prefer to use multiple indexes!
If you have a column that you want to be a foreign key, it should be indexed alone. You may also want to use the column as part of a compound key for the table. This is often done for implementing m:n relations. See, for example, the MySQL man page on foreign keys for an example. (Search the page for "Foreign Keys and ALTER TABLE")
Whay if I have a big table, and some queries search for a range of values, and some queries need to access a single row fast - wouldn't it be usefull to hold both a b-tree and a hash index of the same column?
@strum - I suppose this might be an argument in favor of multiple indexes. However, MySQL supports hash indexes only for MEMORY tables; using a MEMORY model for a "big table" is already questionable (you'd probably be better of with a MySQL Cluster). Plus you need to balance the potential benefits against the costs (updates are more expensive; queries need more processing to figure out what index to use). That's all assuming that it works at all and that you can control which index gets used. (I've never tried creating two different index types on the same column.)
1

Theoretically it can be a good idea to have a reverse index on a column as well as the normal index. Not sure if its supported by MySQL though.

It depends what you are seraching for. If you are expecting the user to search for lastnames, and you store first and last names in the same column, then many searches will be of the form

LIKE %lastname

In that case, a normal index will not help much, because it builds the index from the beginning of the string. It will need to look through every record to see that it at some point doesn't contain the search string. A reverse index, will be useful, because it indexes from the back and foward. Using double indexes would speed up this particular kind of search.

With wildcards at both the beginning and the end.

Improve this answer

1 Comment

Reverse indices are not possible in MySQL. Well, MySQL 8 has a "reverse" index, but it is not what you describe, it is just a reversed order, nothing to do with prefix/suffix in varchars.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.