CODEWITHSUNDEEP
mysqlsqljoin
Iulia Jalba
Iulia Jalba

Reputation: 23

SQL join not working (or very slow)

I have the following tables in mysql:

Table A:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | varchar(50) | YES  |     | NULL    |       |
| type  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Table B:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| channel | varchar(20) | YES  |     | NULL    |       |
| sid     | varchar(50) | YES  |     | NULL    |       |
| type    | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

I want to find the rows from A that have an entry in B with the same sid. I tried the following Join command:

SELECT A.sid FROM A join B on A.sid=B.sid;

This query never gives me the answer. Tabe A has 465420 entries and table B has 291326 entries.

Why does it not work? Are there too many entries? Or does it have anything to do with the fact that I have no primary keys assigned?

Upvotes: 0

Views: 53

Answers (2)

Nik
Nik

Reputation: 371

Try 

    SELECT A1.sid 
    FROM (select A.sid from A order by sid) A1 
    join (select B.sid from B order by sid) B1 
    on A1.sid=B1.sid;

Else above holds true. You need index.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your query is fine. You would appear to need an index. I would suggest B(sid).

You can also write the query as:

select a.sid
from a
where exists (select 1 from b where a.sid = b.sid);

This will not affect performance -- unless there are lots of duplicates in b -- but it will eliminate issues caused by duplicates in b.

Upvotes: 4

Related Questions