mysql - 提高mysql查询性能

Question

我用过这个查询

SELECT  COUNT(CASE WHEN C <= 1 THEN 1 END) AS nooffamiliesHavingcount1,
        COUNT(CASE WHEN C BETWEEN 2 AND 4 THEN 1 END) AS nooffamiliesHavingcountbetween2And4,
        COUNT(CASE WHEN C > 4 THEN 1 END) AS nooffamiliesHavingcountgreaterthan3
FROM    (   SELECT  COUNT(*) AS C
            FROM    user where user_id = (select user_id from location where location_id in(select location_id from country where state_name='STATE'))
            GROUP BY House_No
        ) t

Here sub query returning approximately 10000 records . The user table has 10,00,000 records. It is taking too much time.Then the error it is saying is server gone away. I am using mysql.

我从谷歌搜索。但我没有运气。

我需要对我的表做哪些更改。我如何才能成功执行此查询 提高查询性能。请给我建议。提前致谢....

Answer 1

试试这个查询

SELECT 
  COUNT(CASE WHEN C <= 1 THEN 1 END) AS nooffamiliesHavingcount1,
  COUNT(CASE WHEN C BETWEEN 2 AND 4 THEN 1 END) AS nooffamiliesHavingcountbetween2And4,
  COUNT(CASE WHEN C > 4 THEN 1 END) AS nooffamiliesHavingcountgreaterthan3
FROM  
  (SELECT 
    COUNT(*) AS C
  FROM 
    user u,  
    location l, 
    country c 
  where 
    l.state_name='STATE' AND 
    l.some_other_column_id= 4 AND  <------- Add your condition
    c.location_id = l.location_id AND 
    u.user_id = l.user_id 
  GROUP BY 
    u.House_No) t

使用适当的连接，因为它很容易理解..

SELECT 
  COUNT(CASE WHEN C <= 1 THEN 1 END) AS nooffamiliesHavingcount1,
  COUNT(CASE WHEN C BETWEEN 2 AND 4 THEN 1 END) AS nooffamiliesHavingcountbetween2And4,
  COUNT(CASE WHEN C > 4 THEN 1 END) AS nooffamiliesHavingcountgreaterthan3
FROM  
  (SELECT 
    COUNT(*) AS C
  FROM 
    user u
  INNER JOIN  
    location l
  ON 
    l.state_name='STATE' AND 
    l.some_other_column_id= 4   <------- Add your condition
    u.user_id = l.user_id 
  INNER JOIN
    country c 
  ON 
    c.location_id = l.location_id 
  GROUP BY 
    u.House_No) t

已编辑

在大多数情况下，JOIN 比子查询更快，而且子查询更快的情况很少见。我接受使用子查询更合乎逻辑且更容易理解，但在性能方面它不如连接好。如果您使用连接，您的数据库将自行优化您的查询，而子查询则不是。尝试对您的两个查询都使用 explain，您将清楚地了解查询是如何执行的。

希望这有助于...