I know this is probably something stupid on my part, but please bear with me. I have basically 3 tables (let's simplify things):

user_id | account_id | phone | name | ...

account_id | name | ...

phone | country_code | ...

Let's pretend we have a simple query like this:

SELECT u.*, a.* FROM users u
LEFT JOIN accounts a ON a.account_id = u.account_id
ORDER BY u.user_id DESC

This correctly assesses that the query needs to use eq_ref on account_id and properly selects only 100 records and is almost instant. However, when I want to do this (this is a simplified version of what I want to use):

SELECT u.*, p.* FROM users u
LEFT JOIN phonedetails p ON p.phone = u.phone
ORDER BY u.user_id DESC

It suddenly tries to go through Full Table Scan (so it's about 300 000 records at the moment) and then does its job. Sure, it works, but it takes almost 4 seconds for whatever reason!

account_id => type int in both tables (users, accounts)
phone => varchar(24) in both tables (users, phonedetails)

It doesn't seem to matter if I have an index on that column or not. Funny thing is that if I do something like this:

SELECT u.*, (SELECT p.country_code FROM phonedetails p WHERE p.phone = u.phone)
FROM users u
ORDER BY u.user_id DESC

It correctly joins the tables using eq_ref and it's fast.

Is there something I can do to improve on the query performance?

Edit with more info:

Interesting (at least for me), is that if I execute this query:

SELECT u.* FROM users u
LEFT JOIN phonedetails p ON p.phone = u.phone
ORDER BY u.user_id DESC

Explain shows those tables are joined correctly with eq_ref on the primary key and it's fast again. Of course, it's useless for me as I don't get the columns I need.

  • Is phone in the phonedetails table indexed? Apr 28, 2017 at 11:42
  • @JonathanFite, yes, in the users table. In phonedetails table it's a Primary Key.
    – walther
    Apr 28, 2017 at 11:43
  • Try this: SELECT u.*, p.* FROM (SELECT * FROM users ORDER BY user_id DESC LIMIT 100) u LEFT JOIN phonedetails p ON p.phone = u.phone ORDER BY u.user_id DESC LIMIT 100 ; Apr 28, 2017 at 13:22
  • Can you share out put of SHOW CREATE TABLE <table name> for all three tables?
    – thatsaru
    Apr 28, 2017 at 13:26
  • Do you need all columns from phonedetails? Because it sounds like maybe it's doing a full table scan because it knows it needs all the phone columns. Is the primary key clustered?
    – RDFozz
    Apr 28, 2017 at 14:29

2 Answers 2


OK, I'll stop trying to figure out why your query is not working and give you a technique for solving it...

SELECT  u.*, p.*
       ( SELECT  user_id   -- minimal data (just PK in this case)
            FROM  users
            ORDER BY  user_id
            LIMIT  100    -- now in subquery
        ) AS x
    JOIN  users AS u  ON u.user_id = x.user_id
    JOIN  phonedetails AS p  ON p.phone = u.phone
    ORDER BY  x.user_id   -- yes, need extra ORDER BY

The general principle: When doing a LIMIT or GROUP BY and the query is even somewhat complex, it may be better to build a subquery that identifies which rows as simply as possible. Then do the JOIN, etc, to get the rest of the data.

  1. The subquery (called a "derived" table in this syntax) finds the user_ids -- and only the 100 needed. Since you are doing LEFT, there is no need to involve phonedetails in this query.
  2. Then reach out to the other tables to get the desired columns.

Caveat: This version of the query may be slow for the same yet-to-be-discovered reason. This reformulation is effective in some situations.


I ran into a similar issue. Explain was showing the ref as null with type of index, despite the column being my primary key. I noticed a collation difference. Once I made the collations match explain would return a type or eq_ref and ref of the joined field. Leaving this here in case anyone else runs across this hunting down similar issue to check the collations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.