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.
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 :)
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With