Por que é mysql ignorando a tecla 'óbvia' para usar neste simples consulta de junção?

votos
5

Eu tenho o que eu pensei que seria uma consulta simples, mas é preciso 'para sempre'. Eu não sou grande com otimizações SQL, então eu pensei que eu poderia pedir a vocês.

Aqui está a consulta, com EXPLICAR:

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;

Resultado:

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

Chaves na firms_location:

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

Chaves na firms_firmphonenumber:

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

Parece (para mim) que o MySQL se recusa a usar a chave primária da tabela firms_location - mas não tenho idéia do porquê.

Qualquer ajuda seria muito apreciada.


Editar após a solução postada

Com o fim alterada por:

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;

Resultado:

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,

Por que decidir usá-los agora? mySQL faz algumas escolhas estranhas ... Qualquer visão ajudaria novamente :)


Editar com detalhe de django

Originalmente, eu tinha esses modelos (abreviado):

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)

Alterar campo Meta.ordering de classe do locaion para (name_en, )fixo a consulta para não ter o fim espúrio por.

Publicado 29/04/2009 em 22:02
fonte usuário
Em outras línguas...                            


4 respostas

votos
4

Essas coisas tendem a ser por tentativa e erro, mas tente encomendar on firms_location.id em vez de firms_firmphonenumber.location_id. Eles são o mesmo valor, mas o MySQL pode, em seguida, pegar no índice.

Respondeu 29/04/2009 em 22:08
fonte usuário

votos
1

Porque não há onde, e porque a cardinalidade do campo de associação é maior do que a do campo juntar, é calculando que ele poderia muito bem ter tudo. Usando o índice na juntar-se não vai acelerar isso, por isso é recorrer ao menor otimização da utilização de um índice para classificação.

Primeiro, você pode fazer USE para forçá-lo a usar o índice que você especificar. Além disso, tente fazer uma optimize para garantir que a cardinalidade está corretamente estimado. (Eu estou supondo que você está usando INNO, que estima-lo em uma série de "mergulhos" aleatórios, se este é MyISAM, que realmente sabe, então eu me pergunto por que a cardinalidade parece que ele faz.)

Não se preocupe indexar o nome ou etc. MySQL irá utilizar apenas um índice por tabela por juntar-se, sempre, e o índice será apenas a granel-lo.

Respondeu 30/04/2009 em 09:38
fonte usuário

votos
1

Ele está usando, para a junção; esse é o 'citiadmin.firms_location.id'valor na refcoluna. Não está aparecendo em possible_keyse keyporque você não tem cláusula WHERE e só está refletindo chaves que tem disponível para a cláusula ORDER BY.

Se você quiser acelerar a sua consulta, tente indexação name_en.

Respondeu 29/04/2009 em 22:10
fonte usuário

votos
0

quantos dados? se apenas algumas linhas, a maioria dos bancos de dados só vai fazer uma varredura na tabela não importa o que você tem índices

Respondeu 29/04/2009 em 22:07
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more