MySQL is searching the tables in incorrect order.
I have this
inner join mst on (mst.pk = trx.fk)
WHERE trx.locid = 'xx'
During EXPLAIN, the result is
(in that order)
I need this
(trx first, before mst)
One possible explanation for why MySQL is scanning
trx, despite that these two tables appear in the opposite order in the query, is that the order of tables does not matter for an
INNER JOIN. For whatever reason, the query optimizer must think it would be faster to join this way.
Primary key columns in MySQL are implicitly indexed, for both MyISAM and InnoDB. So I don't think the
mst.pk column is a problem here. But the
trx.fk column might benefit from an index. To test this hypothesis, you can add an index on that column if you have not already done so:
ALTER TABLE trx ADD INDEX `fk_idx` (fk)
With both join columns indexed, the order of joining should not make much difference and hopefully you will have the performance you need.