View Full Version : MySQL Query Optimisation on Char

10-26-2011, 03:38 PM
I am looking at this question in a test I have been given to look at and cannot for the life of me work out what answer they are looking for. I'm thinking it's something to do with not wrapping quotes around the "to_w_user_id" part of the where clause....

`id` int(11) NOT NULL,
`to_w_user_id` char(20) NOT NULL,
`to_w_user_name` varchar(64) NOT NULL,
`date` int(11) NOT NULL,
`question` int(11) NOT NULL,
`answer` tinyint(2) NOT NULL,
`unlocked` tinyint(1) DEFAULT NULL,
`site_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`q_id`),
UNIQUE KEY `answer`(`id`,`to_w_user_id`,`question`,`answer`,`site_id`)

Has the following query been written optimally? If you think it is not, what should be done to optimize it?

select count(*) from quiz_a where to_w_user_id=100001804947780 and site_id=35;

10-26-2011, 04:15 PM
wrapping to_w_user_id inside quotes would make it a string and not a column name so that would not be correct.

Old Pedant
10-27-2011, 06:12 AM
Given the table as it exists, I don't think there is anything that can be done to improve the query.

However, if you are allowed to change the index(es) on the table, then you could significantly improve performance.

Just change the UNIQUE KEY to

UNIQUE KEY answer(to_w_user_id,site_id,id,question,answer,site_id)
UNIQUE KEY answer(site_id,to_w_user_id,id,question,answer,site_id)

MySQL can only use partial keys if the part(s) to be used are the the first field(s) specified in the key.

Old Pedant
10-27-2011, 06:19 AM
Whoops...I didn't notice that the to_w_user_id field was CHAR(20).

Yes, you should *DEFINITELY* put quotes around the value that field is being compared to.

select count(*) from quiz_a where to_w_user_id='100001804947780' and site_id=35;

The performance gain from that, though, is nearly infintesimal. MySQL will parse the query and, essentially, add the quotes for you. So it's only a parse difference, not a runtime difference.

In that same vein, since CHAR fields are padded with spaces you could get another miniscule improvement by padding the test string to 20 characters:

select count(*) from quiz_a where to_w_user_id='100001804947780 ' and site_id=35;

(5 spaces there).

But honestly, those changes will make so little difference it would be hard to measure. Changing the order of the field in the KEY would be orders of magnitude more improvement.