CODEWITHSUNDEEP
phpmysqlsql
Dom
Dom

Reputation: 1028

Mysql left join taking too long or crashing... please help

Hello all I am creating a report at work and for some reason am running into some serious problems when using a left join. I have two tables, a u_entities table and a u_activities table where there is a one to many relationship from entities to activities. I need to find out which entities do not have an activity associated with it. So I wanted to left join entities to activities and list the activities that are empty. So my query looks like this

              SELECT *
              FROM (SELECT a.activity_id, e.entity_id, e.acc_name
                    FROM u_entity AS e
                    LEFT JOIN u_activity AS a
                    ON e.entity_id = a.account_id) AS i
              WHERE i.activity_id = ''

This just takes way too long so just to see the results of the left join I tried to just run the sub select in phpmyadmin which looks like this

                    SELECT a.activity_id, e.entity_id, e.acc_name
                    FROM u_entity AS e
                    LEFT JOIN u_activity AS a
                    ON e.entity_id = a.account_id

I ran in to the same problem the server just keeps working and it never finishes. I had to take the tables and run the queries on my local machine using xammp because people need the server and it was locking up. When I run the second query in phpmyadmin on my localhost side, my computer crashes. The entities table has about 20,000 records and the actiivty about 80000 but Ive done inner joins on these tables before with no problem I dont see why this is an issue. Maybe my queries are wrong or something. Ive never done a left join before so I wouldnt know. If there is another way to do this that would be cool but I would really like to know why the left join isnt working correctly. Please any response would be greatly appreciated.

Upvotes: 0

Views: 2286

Answers (2)

Ted Hopp
Ted Hopp

Reputation: 234807

It sounds like you want to select everything from u_entities for which there do not exist any rows in u_activities. The way to do this is with a subquery, not any kind of join. Here's one way:

SELECT * FROM u_entities
 WHERE entity_id
       NOT IN (SELECT DISTINCT account_id FROM u_activity)

Here's another:

SELECT * FROM u_entities
WHERE NOT EXISTS (
    SELECT account_id
      FROM u_activity
     WHERE u_activity.account_id = u_entities.entity_id
     LIMIT 1
    )

Upvotes: 1

matei
matei

Reputation: 8685

as a note, a quicker way to get entities that don't have activities is to do 


SELECT e.id, ...
FROM u_entity e
LEFT JOIN u_activity a ON e.entity_id=a.account_id
WHERE ISNULL(a.account_id)

About the running slow problem, you want to make sure that you have created indexes on the columns you are using in the joins (in this case, entity_id and account_id) and that they are up to date ( run repair table u_entity and repair table u_activity - if your tables are MyISAM). You shoud really run an EXPLAIN SELECT... (put all your query after the EXPLAIN part) and post the result here to get better help.

Upvotes: 2

Related Questions