Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

So:

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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=8814 DEFAULT CHARSET=utf8;

Thanks.

share|improve this question
    
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(); –  RolandoMySQLDBA May 20 '13 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. –  Aaron Bertrand May 20 '13 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 May 21 '13 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? –  Aaron Bertrand May 21 '13 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... –  Aaron Bertrand May 21 '13 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)?"

share|improve this answer
    
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. –  Nathan Jolly May 23 '13 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). –  Alain Collins May 23 '13 at 5:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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