I am a beginner on MySQL Administration and performance problems. Now this is confusing me.

I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, user:4 to identify a row in one type of entity.

There are 5 class names for now.

Since the string could be constructed as note:123 or 123:note, either holds the meaning of it, I am wondering does 123:note will have better query performance if I do select * from table where identity='123:note' than the other? I am thinking that 123:note will have more variety from the beginning of string, because the id is at the front.

Or it just doesn't matter?

UPDATE: the identity field is just a redundant field for query, the type and id are actually stored in other two fields in the same table.


1st, where type='note' and id=123' is definitely faster than where identity in ('note:123', 'user:456'). I think it's definitely faster, right?

2nd, if I have to have this redundant column, note:123 or 123:note which pattern is faster when I do where identity in ('note:123', 'user:456'), and the length of identity is 20.

And here is create info: likeable_identity is the identity field I am talking about.

CREATE TABLE `likes` (
  `user_id` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `likeable_id` int(11) DEFAULT NULL,
  `likeable_type` varchar(255) DEFAULT NULL,
  `likeable_identity` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_likes_on_user_id_and_likeable_id_and_likeable_type` (`user_id`,`likeable_id`,`likeable_type`),
  KEY `index_likes_on_user_id` (`user_id`)


  • Please do SHOW CREATE TABLE tblname\G on the table where identity is located. If for security purposes you can't do that, then at least tell me what the length of the VARCHAR field is. You can also run this and post the output in the question body : SELECT identity FROM tblname PROCEDURE ANALYSE(); Commented May 20, 2013 at 16:43
  • Why wouldn't you store these two separate pieces of data in two separate columns? Then you can worry about optimizing searches for one or the other without worrying about how you decided to munge them together for some reason. Commented May 20, 2013 at 17:25
  • @AaronBertrand Yes, they are also stored in other two fields, type and id, this identity field is a redundant field for easier searching, only need one where clause.
    – larryzhao
    Commented May 21, 2013 at 1:59
  • Why are you storing them twice? Why doesn't your query go after the columns where they're stored on their own? Commented May 21, 2013 at 2:00
  • 1
    Then you need to decide whether you want ease of programming or performance. In this case, you can't have both very easily. Do you want it to be a little harder for a query you're going to write once, or do you want to pay a performance penalty every single time it's executed? I'd suggest that changing the query to look at the right column (and getting rid of the absolutely needless redundant storage of the same data) will be worth it. If you don't, shrug... Commented May 21, 2013 at 2:08

1 Answer 1


Several points:

1) Store the data in two columns.

2) Normalize 'type' to a separate table, so you'll only put an INT into your index rather than a varchar(255).

3) Switch to UNSIGNED INT for the ids, and maybe TINYINT for the types.

4) Tell the app people to learn about multiple WHERE clauses for their SQL and give them a good index.

3) Once you do those, your question might become, "should my composite index be (id,type) or (type,id)?"

  • For step 2, you can also use an enum data type in MySQL to get the benefits of an integer for indexing, potentially without needing any changes to your application. Using a separate table is a more robust, database-portable solution though. Commented May 23, 2013 at 5:04
  • There are scary warnings about ALTERing existing enum columns. I use them for sets that never change (yes/no), but not for things that might change (types). Commented May 23, 2013 at 5:13

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.