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 9 of 9
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts

    How to properly index this LEFT JOIN?

    I'm testing out this comment script and the author did not include any indexes. I was able to go through the script and index everything - except the LEFT JOIN.

    PHP Code:
    SELECT DISTINCT pac2_titles.title AS page_titlepac2_notifications.* FROM pac2_notifications LEFT JOIN pac2_titles ON pac2_titles.uri pac2_notifications.what WHERE `what` = '/demo.php' AND `email` != 'hehe@aol.com' 
    Any ideas how to properly index this?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Give as result of EXPLAIN.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Code:
    +----+-------------+--------------------+-------+---------------+------+---------+------------------------------+------+------------------------------+
    | id | select_type | table              | type  | possible_keys | key  | key_len | ref                          | rows | Extra                        |
    +----+-------------+--------------------+-------+---------------+------+---------+------------------------------+------+------------------------------+
    |  1 | SIMPLE      | pac2_notifications | range | what          | what | 919     | NULL                         |    2 | Using where; Using temporary |
    |  1 | SIMPLE      | pac2_titles        | ref   | uri           | uri  | 767     | test.pac2_notifications.what |    1 |                              |
    +----+-------------+--------------------+-------+---------------+------+---------+------------------------------+------+------------------------------+
    2 rows in set (0.00 sec)

    I know the when "using temporary" shows up - it's not good ...

    Any help would be appreciated!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Code:
    SELECT DISTINCT pac2_titles.title AS page_title, pac2_notifications.* 
    FROM pac2_notifications LEFT JOIN pac2_titles 
    ON pac2_titles.uri = pac2_notifications.what
    WHERE what = '/demo.php' AND email != 'hehe@aol.com'
    Okay, I have to ask.

    What does EXPLAIN say if you forget the JOIN and just do
    Code:
    SELECT *
    FROM pac2_notifications 
    WHERE what = '/demo.php' AND email != 'hehe@aol.com'
    I think MySQL has rejected using a key for the pac2_notifications table because it has determined it can get the needed records (it is saying there are only 2 matches?!?) fast enough without using a key.

    And I would *guess* that the reason for the USING TEMPORARY stems from your DISTINCT usage.

    And are you sure you need the DISTINCT?

    Because you are using pac2_notifications.* your DISTINCT will only do something to that table if you have two *IDENTICAL* records in the table.

    I would hope that is extremely unlikely (I'd hope you have a PRIMARY KEY on that table, in fact, making duplicates in that table impossible).

    So the only reason for the DISTINCT would be if you expected to find multiple matches in the other table, pac2_titles, all with the same title value.

    If that's the only reason for your DISTINCT then possibly we can rearrange the query to move it to a subquery. Whether that would help performance or not, I don't know. But it would be worth a shot.
    Last edited by Old Pedant; 01-20-2013 at 05:41 AM.
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    For example:
    Code:
    SELECT P2.title AS page_title, pac2_notifications.* 
    FROM pac2_notifications 
    LEFT JOIN (
        SELECT DISTINCT title FROM pac2_titles 
        WHERE uri = '/demo.php' ) AS P2
    ON pac2_notifications.what = P2.title
    WHERE what = '/demo.php' AND email != 'hehe@aol.com'
    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.

  • #6
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Quote Originally Posted by Old Pedant View Post
    For example:
    Code:
    SELECT P2.title AS page_title, pac2_notifications.* 
    FROM pac2_notifications 
    LEFT JOIN (
        SELECT DISTINCT title FROM pac2_titles 
        WHERE uri = '/demo.php' ) AS P2
    ON pac2_notifications.what = P2.title
    WHERE what = '/demo.php' AND email != 'hehe@aol.com'
    This is the results from the query :



    I'm not sure it needs to be DISTINCT, I'm just in the process of testing this comment script so the database does not have a lot of entries.

    I'll have to do some more tests to see if identical records appear to have a better understanding how this script is operating.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    ??? Why is it getting that "impossible where"?

    It feels like it is saying there are no records matching '/demo.php'.

    What do you get if you *execute* (not explain) just
    Code:
        SELECT DISTINCT title FROM pac2_titles 
        WHERE uri = '/demo.php'
    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.

  • #8
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    I'm sorry, I forgot I was doing a different test I had changed the /demo.php to /demo2.php.

    This is the results from the previous query :


  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Still getting that "impossible where". Which means that if you really run the query you should get zero results. Do you?

    And by the by, I misread your EXPLAIN in your post #3 !! Your query *IS* using a key. It's just that the key length is so long it's not a very efficient key. But it probably can't do any better. And then, as I said in my post #4, I would *guess* that the reason for the USING TEMPORARY stems from your DISTINCT usage.

    In order to perform DISTINCT, MySQL has to sort *ALL* the records (after the JOIN and WHERE are satisified) on *ALL* the fields you specified in your SELECT. Because you included pac2_notifications.*in the SELECT, that means it has to sort on all of those fields. After doing the sort, *then* it runs through all the fields looking for any *EXACT* matches and rejecting all but the first such exact match.

    In other words, DISTINCT is a very very expensive option that should be avoided when possible.
    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
    •