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

FROM trx
inner join mst on ( =
WHERE trx.locid = 'xx'

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

During EXPLAIN, the result is
(in that order)

I need this
(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?


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 column is a problem here. But the 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