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.
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 tblnamePROCEDURE ANALYSE();
– RolandoMySQLDBA May 20 at 16:43type
andid
, thisidentity
field is a redundant field for easier searching, only need onewhere
clause. – larryzhao May 21 at 1:59