I am a beginner on MySQL Administration and performance problems. Now this is confusing me.
I have a
identity, the string in it is consisted of a Class type and an id, like
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
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?
identity field is just a redundant field for query, the
id are actually stored in other two fields in the same table.
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,
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;