开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜