mysql查询第500万行记录_MySQL中的500万行表在select中加入了查询

表格完全填充后,我担心下面的查询的性能.到目前为止,它正处于开发阶段并且在虚拟数据方面表现良好.

表“adress_zoo”将在完全填充后包含大约5亿条记录. “adress_zoo”表看起来像这样：

CREATE TABLE `adress_zoo`

( `adress_id` int(11) NOT NULL, `zoo_id` int(11) NOT NULL,

UNIQUE KEY `pk` (`adress_id`,`zoo_id`),

KEY `adress_id` (`adress_id`) )

ENGINE=InnoDB DEFAULT CHARSET=latin1;

其他表格每个最多包含500条记录.

完整查询如下所示：

SELECT a.* FROM jos_zoo_item AS a

JOIN jos_zoo_search_index AS zsi2 ON zsi2.item_id = a.id

WHERE a.id IN (

SELECT r.id FROM (

SELECT zi.id AS id, Max(zi.priority) as prio

FROM jos_zoo_item AS zi

JOIN jos_zoo_search_index AS zsi ON zsi.item_id = zi.id

LEFT JOIN jos_zoo_tag AS zt ON zt.item_id = zi.id

JOIN jos_zoo_category_item AS zci ON zci.item_id = zi.id

**JOIN adress_zoo AS az ON az.zoo_id = zi.id**

WHERE 1=1

AND ( (zci.category_id != 0 AND ( zt.name != 'prolong' OR zt.name is NULL))

OR (zci.category_id = 0 AND zt.name = 'prolong') )

AND zi.type = 'telefoni'

AND zsi.element_id = '44d3b1fd-40f6-4fd7-9444-7e11643e2cef'

AND zsi.value = 'Small'

AND zci.category_id > 15

**AND az.adress_id = 5**

GROUP BY zci.category_id ) AS r

)

AND a.application_id = 6

AND a.access IN (1,1)

AND a.state = 1

AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-06-07 07:51:26')

AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-06-07 07:51:26')

AND zsi2.element_id = '1c3cd26e-666d-4f8f-a465-b74fffb4cb14'

GROUP BY a.id

ORDER BY zsi2.value ASC

查询通常会返回大约25条记录.

根据您的经验,此查询是否可以接受(在3秒内响应)？

我该怎么做才能优化这个？

正如@Jack所建议我用EXPLAIN运行查询并得到了这个：