Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder crmpicco's Avatar
    Join Date
    Jan 2005
    Location
    Mauchline, Scotland
    Posts
    1,097
    Thanks
    15
    Thanked 1 Time in 1 Post

    MySQL Query Optimisation on Char

    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....

    Code:
    CREATE TABLE `quiz_a` 
    (
    `q_id` int(11) NOT NULL AUTO_INCREMENT, 
    `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`)
    ) ENGINE=InnoDB
    Has the following query been written optimally? If you think it is not, what should be done to optimize it?

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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    wrapping to_w_user_id inside quotes would make it a string and not a column name so that would not be correct.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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
    Code:
    UNIQUE KEY answer(to_w_user_id,site_id,id,question,answer,site_id)
    or
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.

    Code:
    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:
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •