表的联合问题，很慢，怎么优化

[qiang925]

SELECT COUNT(*) FROM backlinkurl LEFT JOIN urlinformation ON (backlinkurl.urlrank=urlinformation.urlid AND backlinkurl.keywordid=urlinformation.keywordid)


里面的表backlinkurl 和 urlinformation 的都是超过 50000条的，联合起来查询，很慢，要怎么优化才比较快?

[ACMAIN_CHM]

语句本身没什么可优化的了。关键看你的索引是否创建了。

explain SELECT COUNT(*) FROM backlinkurl LEFT JOIN urlinformation ON (backlinkurl.urlrank=urlinformation.urlid AND backlinkurl.keywordid=urlinformation.keywordid);

show index from backlinkurl;
show index from urlinformation;

贴出来看一下。

[qiang925]

index of backlinkurl is id( primary)
index of urlinformation is also id( primary)

[qiang925]

再把urlrank　urlid　keywordid　keywordid　设为index,效果好多了

[ACMAIN_CHM]

请如下直接贴你的结果！

mysql> show index from table1;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| table1 |          0 | PRIMARY  |            1 | id          | A         |      3 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.14 sec)

mysql>

[feixianxxx]

[Quote=引用 4 楼 acmain_chm 的回复:]

请如下直接贴你的结果！

SQL code
mysql> show index from table1;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| T……
[/Quote]
大大 你的

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| table1 |          0 | PRIMARY  |            1 | id          | A         |      3 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.14 sec)

这个是从DOS里面黏贴过来的还是哪个工具上来的？

[feixianxxx]

[Quote=引用 3 楼 qiang925 的回复:]

再把urlrank　urlid　keywordid　keywordid　设为index,效果好多了
[/Quote]

一般我们的索引键在联接字段 分组字段 排序字段和 where条件筛选字段

[libenyue]

你这个到底是统计什么啊，如果要是统计两表相同的数据，用inner join

[shine333]

[Quote=引用 2 楼 qiang925 的回复:]

index of backlinkurl is id( primary)
index of urlinformation is also id( primary)
[/Quote]
LZ的意思是两张表，除了PK，就没有index了？？？！！！