mysql不同服务器查询_mysql解释不同服务器上的不同结果,同一查询,同一个数据库...

经过大量的工作,我终于得到了一个相当复杂的查询,非常流畅地工作,并很快返回结果.

它在开发和测试方面运行良好,但现在测试速度已大大减慢.

解释查询在开发上需要0.06秒并且在测试中大致相同,现在测试时间为7秒.

解释略有不同,我不知道为什么会这样

来自开发者的解释

-+---------+------------------------------+------+------------------------------

---+

| id | select_type | table | type | possible_keys | key

| key_len | ref | rows | Extra

|

+----+-------------+------------+--------+-------------------------+------------

-+---------+------------------------------+------+------------------------------

---+

| 1 | PRIMARY | | ALL | NULL | NULL

| NULL | NULL | 5 |

|

| 1 | PRIMARY | tickets | ref | biddate_idx | biddate_idx

| 7 | showsdate.bid,showsdate.date | 78 |

|

| 2 | DERIVED | shows | ALL | biddate_idx,latlong_idx | NULL

| NULL | NULL | 3089 | Using temporary; Using fileso

rt |

| 2 | DERIVED | genres | ref | bandid_idx | bandid_idx

| 4 | activehw.shows.bid | 2 | Using index

|

| 2 | DERIVED | artists | eq_ref | bid_idx | bid_idx

| 4 | activehw.genres.bid | 1 | Using where

|

+----+-------------+------------+--------+-------------------------+------------

并在测试中

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5 | |

| 1 | PRIMARY | tickets | ref | biddate_idx | biddate_idx | 7 | showsdate.bid,showsdate.date | 78 | |

| 2 | DERIVED | genres | index | bandid_idx | bandid_idx | 139 | NULL | 531281 | Using index; Using temporary; Using filesort |

| 2 | DERIVED | artists | eq_ref | bid_idx | bid_idx | 4 | activeHW.genres.bid | 1 | |

| 2 | DERIVED | shows | eq_ref | biddate_idx,latlong_idx | biddate_idx | 7 | activeHW.artists.bid | 1 | Using where |

+----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+

5 rows in set (6.99 sec)

即使查询完全相同,表的顺序也不同.

这会导致经济放缓吗？如果是的话,我该如何解决？

开发是windows,测试是centOs.

两个都运行相同版本的mysql 5.0,就像我说的,测试运行完美,我没有对数据库进行任何结构更改.

我运行mysqlcheck,所有表都回来了.