Geek Answers Handbook

Why does mysql ignore the "obvious" key to use in this simple connection request?

I have what I would consider a simple request, but it takes "forever." I'm not very good at optimizing SQL, so I thought I might ask you guys.

Here's a 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 company_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 firm_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 company_location table primary key, but I have no idea why.

Any help would be greatly appreciated.

Edit after published solution

Reordered:

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 he decide to use them now? mySQL makes some odd variations ... Any insight will help again :)

Edit using parts from django

Initially, I had these (shortened) 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")

Locaion. Meta.Adording ("name_en", ) , .

+5
4

, , , _location.id, firm_firmphonenumber.location_id. , MySQL .

+4

; 'citiadmin.firms_location.id' ref. possible_keys key, WHERE, , ORDER BY.

, name_en.

+1

, , , , , . , .

-, USE, . , , , . ( , INNO, "", MyISAM, , , , .)

.. MySQL , .

+1
source

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

0
source

More articles:


All Articles