Why is mysql ignoring the 'obvious' key to use in this simple join query?

Question

Hello. I have what I'd thought would be a simple query, but it takes 'forever'. I'm not great with SQL optimizations, so I thought I could ask you guys.

Here's the query, with EXPLAIN:

EXPLAIN SELECT *
    FROM `firms_firmphonenumber`
    INNER JOIN `firms_location` ON (
        `firms_firmphonenumber`.`location_id` = `firms_location`.`id`
    )
    ORDER BY
         `firms_location`.`name_en` ASC,
         `firms_firmphonenumber`.`location_id` ASC LIMIT 100;

Result:

id, select_type,       table,           type,  possible_keys,                     key,                           key_len, ref, rows, Extra
1,  'SIMPLE',     'firms_location',    'ALL',  'PRIMARY',                        '',                            '',             '', 73030, 'Using temporary; Using filesort'
1,  'SIMPLE', 'firms_firmphonenumber', 'ref', 'firms_firmphonenumber_firm_id', 'firms_firmphonenumber_firm_id', '4', 'citiadmin.firms_location.id', 1, ''

Keys on firms_location:

Keyname                 Type Unique Packed Field Cardinality
PRIMARY                    BTREE    Yes     No      id      65818
firms_location_name_en     BTREE    No      No      name_en 65818

Keys on firms_firmphonenumber:

Keyname                     Type  Unique Packed  Field       Cardinality
PRIMARY                         BTREE Yes    No      id          85088
firms_firmphonenumber_firm_id   BTREE No     No      location_id 85088

It seems (to me) that mySQL refuses to use the firms_location table's primary key - but I have no idea why.

Any help would be much appreciated.

---

Edit after solution posted

With the altered order by:

EXPLAIN SELECT *
    FROM `firms_firmphonenumber`
    INNER JOIN `firms_location` ON (
        `firms_firmphonenumber`.`location_id` = `firms_location`.`id`
    )
    ORDER BY
         `firms_location`.`name_en` ASC,
         `firms_location`.id ASC LIMIT 100;
         #`firms_firmphonenumber`.`location_id` ASC LIMIT 100;

Result:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","firms_location","index","PRIMARY","firms_location_name_en","767","",100,""
1,"SIMPLE","firms_firmphonenumber","ref","firms_firmphonenumber_firm_id","firms_firmphonenumber_firm_id","4","citiadmin.firms_location.id",1,""

Why did it decide to use these now? mySQL makes some odd choices... Any insight would help again :)

---

Edit with detail from django

Originally, I had these (abbreviated) models:

class Location(models.Model):
    id = models.AutoField(primary_key=True)
    name_en = models.CharField(max_length=255, db_index=True)
    class Meta:
        ordering = ("name_en", "id")

class FirmPhoneNumber(models.Model):
    location = models.ForeignKey(Location, db_index=True)
    number = PhoneNumberField(db_index=True)
    class Meta:
        ordering = ("location", "number")

Changing the Locaion's class's Meta.ordering field to ("name_en", ) fixed the query to not have the spurious order by.

Answer 1

how much data? if only a few rows, most databases will just do a table scan no matter what indexes you have

Answer 2

These things tend to be by trial and error, but try ordering on firms_location.id rather than firms_firmphonenumber.location_id. They are the same value, but MySQL may then pick up on the index.

Answer 3

It is using it, for the join; that's the 'citiadmin.firms_location.id' value in the ref column. It isn't appearing in possible_keys and key because you have no WHERE clause and it's only reflecting keys it has available for the ORDER BY clause.

If you want to speed up your query, try indexing name_en.

Answer 4

Because there's no where, and because the cardinality of the join field is higher than than of the joining field, it's calculating that it might as well get everything. Using the index on the join won't speed that up, so it's resorting to the lesser optimization of the using an index for sorting.

First, you can do USE to force it to use the index you specify. Also, try doing an optimize to make sure the cardinality is correctly estimated. (I'm guessing you're using INNO, which estimates it in a series of random "dives"; if this is MyISAM, which actually knows, then I wonder why the cardinality looks as it does.)

Don't bother indexing the name or etc. MySQL will use only one index per table per join, ever, and the index will just bulk it up.