MySQL index with two same columns

Question

I have a table with several hundred million rows. One of the columns is `status` varchar(10).

Most values in the status are 1 character, some varying up to 10. However a subset of the values has a pattern of its own. A whole group of status values begin with a single character c followed by a number ranging from 0 to 10,000.

I would like to index this column with the following:

ALTER TABLE tbl ADD KEY (status(1), status);

This would be better than having two individual keys, one on status(1) (first character of the whole column) and second status. Together they would always be faster.

However MySQL prohibits me from creating such:

ERROR 1060 (42S21): Duplicate column name 'status'

How can I solve this?

Joe Stefanelli · Accepted Answer · 2011-10-20 19:33:48Z

1

There's really no reason to index status(1) independently of status. One index created on status should handle both situations equally well.

answered Oct 20, 2011 at 19:33

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

3 Comments

Mikhail Over a year ago

To me that seems like having an index for each individual name in the phonebook -- would take comparably long to search the index as the database. Is this wrong thinking?

Joe Stefanelli Over a year ago

@Mikhail Yes, that thinking is wrong. Any search on status, independent of how it is formatted, can take advantage of the index.

Mikhail Over a year ago

I understand that it can take advantage of it. What you're saying is that looking up the first character, and then the rest of the value isn't any faster?

Poodlehat · Accepted Answer · 2011-10-20 19:39:04Z

1

You could create a second column in your table and populate it with the first character of the other column and then create an index on each. However, this might have poor selectivity and not be all that useful.

answered Oct 20, 2011 at 19:39

Poodlehat

3601 gold badge2 silver badges9 bronze badges

2 Comments

Mikhail Over a year ago

I thought about that, but to take advantage of this new column I would have to rewrite all the code that uses this

Poodlehat Over a year ago

If you are doing a like 'c%' type select, it isn't buying you anything to make a separate index as they will be ordered together. If, however, you wanted to search for the last character, then something like splitting off a second column might be useful since using a % at the head of a like requires scanning ALL the items.

Collectives™ on Stack Overflow

MySQL index with two same columns

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related