How to properly index this LEFT JOIN?

[Mayhem30]

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_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' 


Any ideas how to properly index this?

[BubikolRamios]

Give as result of EXPLAIN.

[Mayhem30]

+----+-------------+--------------------+-------+---------------+------+---------+------------------------------+------+------------------------------+
| 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!

[Old Pedant]

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

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.

[Old Pedant]

For example:

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'

[Mayhem30]

Originally Posted by Old Pedant

For example:

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.

[Old Pedant]

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

    SELECT DISTINCT title FROM pac2_titles 
    WHERE uri = '/demo.php'

[Mayhem30]

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 :

[Old Pedant]

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.