CODEWITHSUNDEEP
mysqlsqldatabase
Jake
Jake

Reputation: 3486

Why does this query make my whole database freeze?

SELECT * FROM `groupon-spain-6sep-2011`, `Hoja1`,`GroupaliaJuly2011` 
WHERE `groupon-spain-6sep-2011`.`code`= 5654 
    OR  `Hoja1`.`code` = "5654 
    OR `GroupaliaJuly2011`.`code` = 5654

Upvotes: 2

Views: 1114

Answers (3)

Derrick Zhang
Derrick Zhang

Reputation: 21501

The query you posed involves the Cartesian product(also known as CROSS JOIN in standard SQL, thanks for the advice @onedaywhen) of three tables, the amount of record involves will be size(A) * size(B) * size(C). So if the total product is large enough, it will fill a significant amount of memory, which will make your database unable to response to any other request, and that will lead to the 'freeze' of your database.

I noticed that you want either of the three specified columns to be the same value '5654', so you can selet tuples from three table respectively, and then UNION them, instead of doing Cartesian product of them like you're doing it now, since I don't think the Cartesian product you're making makes any sense. That will save a GREAT number of memory.

Upvotes: 6

jgauffin
jgauffin

Reputation: 101130

You might want to do a union instead:

SELECT * 
    FROM groupon-spain-6sep-2011
    WHERE code = 5654
UNION SELECT * 
    FROM Hoja1
    WHERE code = 5654
UNION SELECT * 
    FROM GroupaliaJuly2011
    WHERE code = 5654

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 452978

You are missing any join condition relating the tables to each other so are doing a cartesian join of 3 tables.

I recommend always using explicit join syntax

SELECT *
FROM   `groupon-spain-6sep-2011`
       JOIN `hoja1` ON `groupon-spain-6sep-2011`.foo=`hoja1`.foo
       JOIN `groupaliajuly2011` ON `groupaliajuly2011`.`foo` = `hoja1`.foo
WHERE  `groupon-spain-6sep-2011`.`code` = 5654
        OR `hoja1`.`code` = 5654
        OR `groupaliajuly2011`.`code` = 5654

Although it is possible that you might be wanting a Union here I think anyway?

Upvotes: 9

Related Questions