Geek Answers Handbook

MySQL more than one INDEX key for one column

Is it correct to have multiple INDEX keys for the same column in a MySQL database?

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

Multiple INDEX keys created for id column

I would like to know if this is normal, and are there any effects or side effects on my script or server using this method?

I use this method to group columns for each index.

+4
source share
2 answers

Indexing a single column is twice useless, slows down inserts and updates, because now you need (useless) to support two indexes and probably confuse the optimizer (if it doesn’t actually break something). On the other hand, it is good (and often useful) to have an index indexed by one, and then also as part of one or more compound keys.

+9
source

It could theoretically be a good idea to have a reverse index for a column, as well as a normal index. Not sure if MySQL supports it.

It depends on what you are sebering for. If you expect the user to search for the last names and you save the first and last names in one column, then many search queries will look like

 LIKE %lastname

In this case, a normal index will not help, because it builds the index from the beginning of the line. He will have to look at each entry to see that at some point it does not contain a search string. A reverse index will be useful because it indexes from the back and flashlight. Using double indexes will speed up this particular kind of search.

With wildcards at both the beginning and the end.

+1
source

More articles:


All Articles