user7754822 user7754822 -4 years ago 87
MySQL Question

How to tell MySQL which table to search first

MySQL is searching the tables in incorrect order.

I have this

SELECT *
FROM trx
inner join mst on (mst.pk = trx.fk)
WHERE trx.locid = 'xx'


This query is long, because MySQL is searching the table
mst
first before cross referencing with
trx
, instead of the other way around.

During EXPLAIN, the result is
SIMPLE mst
SIMPLE trx
(in that order)

I need this
SIMPLE trx
SIMPLE mst
(trx first, before mst)


This is weird, because all my other SQL statements are doing fine, except for this one.

Can we tell MySQL to filter which table first, and then reference the other table next?

Thanks!

Answer Source

One possible explanation for why MySQL is scanning mst before 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download