MyISAM Foreign Key like functionality
I have a table called TABLE1
which have 5 columns that I rarely use and design-wise should b开发者_运维问答e separated from that table. Therefore, I created a new table called TABLE-REF
where its primary key (called refID
) is actually the ID of the corresponded columns in TABLE1. That way I separated these 5 columns from my main table. I did not use a foreign key since I am using MyISAM.
On the rare occasions when I do need to select these 5 columns for rows in TABLE1, what will be the most efficient way to select them?
SELECT * FROM TABLE-REF WHERE refID={$table1ID}
Beside indexing the column refID
, is there any other improvement I can make to improve the complexity of the query?
You can get both rows as one if you use a join. so no more back to the database for each row in TABLE to get the TABLE-REF row.
SELECT
T.*, R.*
FROM `TABLE` T
INNER JOIN `TABLE-REF` R ON R.refID = T.ID
WHERE <your conditions>
精彩评论