Mysql自加入性能 [英] Mysql Self-Join Performance

问题描述

假设我有一张桌子，像这样:

Let's say I have a table looks like this:

+----+----------+------------+-------+------+
| id | category |      state | A1code| val  |
+----+----------+------------+-------+------+
|  1 |        1 |    Florida | 13000 | 12   |
|  2 |        1 |    Florida | 13001 | 14   |
|  3 |        1 |    Florida | 13002 | 15   |
|  4 |        2 |    Florida | 13000 | 12   |
|  5 |        2 |    Florida | 13001 | 17   |
|  6 |        2 |    Florida | 13002 | 16   |
|  7 |        1 |  Calfornia | 13000 | 15   |
|  8 |        1 |  Calfornia | 13001 | 13   |
|  9 |        1 |  Calfornia | 13002 | 14   |
| 10 |        2 |  Calfornia | 13000 | 12   |
| 11 |        2 |  Calfornia | 13001 | 14   |
| 12 |        2 |  Calfornia | 13002 | 16   |
....
+----+----------+------------+------+

我需要以这种方式获得结果:

and I need to get the result in this fashion:

state, A1code, category1, category2
Florida,13000, 12,12
Florida,13001, 14,17
Florida,13002, 15,16
Calfornia,13000, 15,12
Calfornia,13001, 13,14
Calfornia,13002, 14,16
....

我目前正看到这样的sql:

and im currently seeing the sql like this:

SELECT A.STATE, A.A1CODE, A.val AS category1, B.val AS category 2
FROM DUMMY_TABLE A
INNER JOIN DUMMY_TABLE B
USING (STATE,A1CODE)
WHERE A.category = 1 AND B.category = 2;

，并且有一个约60k长的表，此查询需要约40秒的时间才能在计算机上运行.

and with a table some 60k long, this query takes ~40secs to run on the computer.

现在带有

SELECT A.STATE, A.A1CODE, A.val AS category1
FROM DUMMY_TABLE A
WHERE A.category = 1

以不到0.1秒的速度运行，而我想要的只是将两个类别的结果组合在一起，必须有一个更快的方法来做到这一点?

running at less than 0.1sec, and what I want is just combining results from two category, there must be a faster way to do this?

(当我尝试将数据库从MS ACCESS移植到MYSQL时会出现此问题.同一查询在MYSQL上花费了约40秒钟，在MS ACCESS中运行需要约1秒的时间.)

(this problem comes when i try to port a db from MS ACCESS to MYSQL. the same query that took ~40s on MYSQL takes ~1sec to run in MS ACCESS.)

预先感谢

推荐答案

尝试以下变体

  SELECT A.STATE, A.A1CODE, A.val AS category1, B.val AS category 2
    FROM (select A.STATE A.A1code, A.val as category1 from DUMMY_TABLE A  where A.category = 1 ) as A
    LEFT JOIN DUMMY_TABLE B
    USING (STATE,A1CODE)
    WHERE B.category = 2  ;

它必须快得多.

但是实际上取决于1)您在第一个查询中有多少列2)您是否在A1CODE，STATE上具有索引.

But actualy that depend of 1) how much column you have in first query 2) do you have indexes on A1CODE,STATE or not.

所以要加快速度，请尝试

so to speedup try

create index DUMMY_TABLE_get on DUMMY_TABLE(A1CODE,STATE);

还有一些方法可以将查询速度提高到10-100毫秒，但是它们需要添加附加字段/触发器.因此，如果您不想每分钟都获得该查询，就不会做任何事情.

There are also some methods to speedup this query upto 10-100ms, but they require add aditional fields/triggers. so no sence do that if you not gooing to get that query every minute.

请注意，即使您的要求，40秒也太多了.可能您已雇用mysql管理员来调整mysql服务器(使用更多RAM进行连接，增加键缓冲区等)

Note, that 40sec is too much even for your request. Probably you have hire mysql administrator to tune your mysql server(use more RAM for joins, increase keybuffer etc)

这篇关于Mysql自加入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！