MYSQL JOIN. How do I only join the first match? (Connection limitation 1)

I have a table with 1,000,000 records. ( t1

) have a "select query" which is very fast and it returns me one id column result. (as a result I only have unique IDs. For example: 1,2,5,10, .. (do not have duplicate values) )

EXPLAIN SELECT SHOWS ME ONLY type index

and ref

(perfect query :))

But I need to filter these results from records this way: inner join to another table 1,000,000 ( t2

)

ON (`t1`.`some_column_id` = `t2`.`ref_id` AND `t1`.`user_id` = 4)

      

He shows me all the matches t1

. some_column_id

= t2

. ref_id

Even when I use

GROUP BY `t1`.`some_column_id` AND/OR LIMIT of 30 ROWS

      

mysql server crash

What do you intend to do to restrict this INNER JOIN to only the first match?

+3


source to share


1 answer


With the information available, I was able to find this solution. It will probably take a while for such a large database to run. I am still looking for an alternative alternative.

Assuming the ' some_column_id

' or ' ref_id

' field is not unique. First make them unique by choosing exactly one id

(primary key) with some aggregate function such as MIN()

. Use it like this:

SELECT tTwo.* FROM (SELECT notUnique, MIN(id) AS min FROM t GROUP BY notUnique) tOne INNER JOIN t AS tTwo ON (tTwo.id=tOne.min)

      

Please note that the above query is applied to only one table to make this column unique.



After applying it for tables as required, you just need to do this

SELECT * FROM t1_unique INNER JOIN t2_unique ON (t1_unique.some_column_id = t2_unique.ref_id)

      

where t1_unique

and t2_unique

will have unique values ​​for some_column_id

and ref_id

respectively.

+2


source







All Articles