Re: Fast 3 Table Join
>That is a totally different situation. So, let's start over. You want to optimize this, correct? Completely correct? No GROUP BY, ORDER BY, or LIMIT, correct?
Yes that's correct
>Do you really use "*"; if you don't, that could impact the optimization.
No ususally I only read in a subset of the columns i just put "*" to make the query simple here
>Stick EXPLAIN in front of the SELECT and provide us with the output
Here is the output:
mysql> EXPLAIN EXTENDED SELECT * FROM arms_length_resale, hmda_purchase, dq_hmda_crosswalk_sales WHERE id_arms_length_resale = idDQ AND id_hmda_purchase = idHMDA AND MM_FIPS_STATE_CODE = 6;
+----+-------------+-------------------------+--------+---------------+---------+---------+----------------------------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+---------------+---------+---------+----------------------------------------------------+----------+-------------+
| 1 | SIMPLE | arms_length_resale | ref | PRIMARY,fips | fips | 9 | const | 10577882 | Using where |
| 1 | SIMPLE | dq_hmda_crosswalk_sales | eq_ref | idDQ,idHMDA | idDQ | 8 | DataQuick.arms_length_resale.id_arms_length_resale | 1 | |
| 1 | SIMPLE | hmda_purchase | eq_ref | PRIMARY | PRIMARY | 8 | DataQuick.dq_hmda_crosswalk_sales.idHMDA | 1 | |
+----+-------------+-------------------------+--------+---------------+---------+---------+----------------------------------------------------+----------+-------------+
It is reading the tables in the order that you suggested. The index called "fips" is on the "State" variable which is MM_FIPS_STATE_CODE. The indexes idDQ and idHMDA are the indexes for what we have been calling idA and idC respectively. These id variables are also indexed (since they are the primary keys) in tables "A" and "C" and are called id_arms_length_resale and id_hmda_purchase respectively.
Subject
Written By
Posted
Re: Fast 3 Table Join
March 24, 2012 10:04PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.