LEFT JOIN or REGULAR JOIN, and how to compare MySql table to an array? Need performance!
I have six tables: t1, t2, t3, t4, t5, t6. And I also have one main table: main_table. TOTAL 7 TABLES!
Now, I am using SOLR for the searching of classifieds, and the results from solr are put into an array. The array results are ID:nrs which I use to match agains the same ID:s in MySql.
The first开发者_运维知识库 column of ALL tables in MySql is called classified_id.
If the user searches for "cars", then Solr will find all cars classifieds, put the id:s into an array and finally compare the MySql main table to match everything in table t1 (which is the cars table) where classified_id is the same in both tables.
The SOLR results array is first imploded, then:
SELECT * FROM classified, t1 WHERE classified.ad_id IN ('$solr_id_arr_imploded') AND classified.classified_id=t1.classified_id
My Q is, is this how I should do this? Is this how I should JOIN here, or use a LEFT JOIN? Is there any faster way of comparing to the array?
the table t1
may be empty if there are no car-classifieds...
Remember, the query could become very very long, for example if SOLR returns 10000 matches, then an array of 10000 id numbers which look like this would be returned: bmw_m3_low_miles_8948939
Thanks
An alternative to IN
is to create a temporary table, fill it with the IDs, and perform an inner join on that. Make sure the other tables have an index on classified_id
. To compare the options, use EXPLAIN
.
精彩评论