CODEWITHSUNDEEP
mysqlleft-join
Brian Smith
Brian Smith

Reputation: 1481

MySQL - How to index this left join?

I'm having trouble indexing this left join :

SELECT comments.id, comments.topid, comments.username, comments.body, comments.dt, comments.active, users.email
FROM comments
LEFT JOIN registered_users.users
ON comments.username = users.username
WHERE postid = 12 AND active = 1
ORDER BY id desc

I have indexes on:

Comments -> keyname (postid) - postid, active, id

Users -> keyname (username) - username

The result I'm getting back is :

+----+-------------+----------+------+---------------+--------+---------+-------------+------+---------------------------------+
| id | select_type | table    | type | possible_keys | key    | key_len | ref         | rows | Extra                           |
+----+-------------+----------+------+---------------+--------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | comments | ref  | postid        | postid | 5       | const,const |  116 | Using temporary; Using filesort |
|  1 | SIMPLE      | users    | ALL  | NULL          | NULL   | NULL    | NULL        |    1 |                                 |
+----+-------------+----------+------+---------------+--------+---------+-------------+------+---------------------------------+

How can I fix this so I'm not "using temporary; using filesort" ?

Upvotes: 2

Views: 16623

Answers (4)

Brian Smith
Brian Smith

Reputation: 1481

In case anyone else has this issue, always make sure your JOIN tables are set to the same collation type.

My comments tables was set to "utf8_unicode_ci" - while I was trying to JOIN to users.email which was set to "latin1_swedish_ci".

If they are set differently, MySQL can't make use of any indexes.

I am now able to use ORDER BY, LIMIT clauses without the dreaded "filesort" issue.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425398

Experience tells me to do this:

create index comments_postid on comments(postid);

And the other index you'd need would be is users(username) but you already have that.

It's a good idea to do this too:

analyze table comments, users;

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

Perhaps making an index with all three columns

comments is an InnoDB table

ALTER TABLE comments ADD INDEX new_index (active,postid,username);

comments is a MyISAM table

ALTER TABLE comments ADD INDEX new_index (active,postid,id,username);

Why propose a new index?

Searching the comments table by the postid index will still require accessing the table intermittently to check the id and username columns. Having more columns from your WHERE and ORDER BY clauses inside the index will lighten the work for the Optimizer.

CAVEAT

Even if the retrieveal is a little faster, the filesort may be unavoidable because you said

ORDER BY id desc

Give it a Try !!!

Upvotes: 1

Steffan Mejia
Steffan Mejia

Reputation: 48

If active is a flag, there may not be enough selectivity for the optimizer to bother with it. Have you tried having the index only on postid?

Upvotes: 0

Related Questions