MySQL does joining on MUL keys impact performance?

I'm using a MySQL query that does three table joins on four tables. Two of these joins are on a pair of MUL keys. The third join is on a pair of primary keys. The query takes a full minute to grab 100 rows.

I believe that both pairs of MUL keys are unique; and so I'm wondering, if these keys were to be indexed as unique, or made into foreign keys, would I notice a significant performance enhancement?

Are the joins on MUL keys likely the culprit here?

Edit

Okay, here's the schema. I substituted letters for the actual table/column names.

mysql> describe table A;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| pkey              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| mkey              | int(10) unsigned | NO   | MUL | NULL    |                |
| a                 | int(10) unsigned | NO   |     | NULL    |                |
| b                 | int(10) unsigned | NO   |     | NULL    |                |

mysql> describe table B;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| pkey              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| mkey1             | int(10) unsigned | NO   | MUL | NULL    |                |
| mkey2             | int(10) unsigned | NO   | MUL | NULL    |                |
| a                 | int(10) unsigned | NO   |     | NULL    |                |
| b                 | int(10) unsigned | NO   |     | NULL    |                |
| c                 | int(10) unsigned | NO   |     | NULL    |                |

mysql> describe table C;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| pkey          | int(10) unsigned | NO   | PRI | NULL    |       |
| mkey          | varchar(128)     | NO   | MUL | NULL    |       |

mysql> describe table D;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| pkey          | int(10) unsigned | NO   | PRI | NULL    |       |
| mkey          | varchar(128)     | NO   | MUL | NULL    |       |

Query:

select
A.a, A.b, B.c, A.mkey, C.pkey, D.mkey
from
A, B, C, D
where
A.pkey=C.pkey and
A.mkey=B.mkey1 and
B.mkey2=D.pkey and
B.a <= A.a and
B.b >= A.b
D.mkey in ('str1', 'str2', ...);

Returns 77 rows.

---

使用显式连接

select
A.a, A.b, B.c, A.mkey, C.pkey, D.mkey
from
A INNER JOIN C ON (A.pkey = C.pkey)
INNER JOIN B ON (A.mkey = B.mkey1)
INNER JOIN D ON (B.mkey2 = D.pkey)
where
B.a <= A.a and
B.b >= A.b
D.mkey in ('str1', 'str2', ...);

链接地址: http://www.djcxy.com/p/21448.html

上一篇: 角度支架MySQL的PHP

下一篇: MySQL会加入MUL密钥来影响性能？