网站建设会遇到哪些问题,泉州网站开发人员,苏州网站建设专家,怎么建做网站我试图优化一个MySQL查询来加速Drupal视图。这些表是节点#xff0c;term_node和term_data。节点有大约500k行#xff0c;term_node大约800k#xff0c;term_data大约300k。查询下方#xff1a;SELECT SQL_NO_CACHE DISTINCT(node.nid) AS nidFROM drupal_node nodeLEFT JO…我试图优化一个MySQL查询来加速Drupal视图。这些表是节点term_node和term_data。节点有大约500k行term_node大约800kterm_data大约300k。查询下方SELECT SQL_NO_CACHE DISTINCT(node.nid) AS nidFROM drupal_node nodeLEFT JOIN drupal_term_node term_node ON node.vid term_node.vidLEFT JOIN drupal_term_data term_data ON term_node.tid term_data.tidWHERE (node.status 1) AND (node.type in (foto_foto))AND ((UPPER(term_data.name) LIKE UPPER(%Hanaa%) ORUPPER(term_data.name) LIKE UPPER(%Bouchaib%)))GROUP BY nidLIMIT 0, 10;此查询需要大约11秒才能显示结果。参与连接的所有列都被编入索引以及term_data.name。node.vidterm_node.vidterm_node.tid和term_data.tid是int(10)term_data.name是varchar(255)。奇怪的是运行这样的组合查询SELECT SQL_NO_CACHE DISTINCT(term_node.tid)FROM drupal_term_data term_dataLEFT JOIN drupal_term_node term_node ON term_node.tid term_data.tidWHERE ((UPPER(term_data.name) LIKE UPPER(%Hanaa%)OR UPPER(term_data.name) LIKE UPPER(%Bouchaib%)))group by term_node.tid和SELECT SQL_NO_CACHE DISTINCT(node.nid) AS nidFROM drupal_node nodeLEFT JOIN drupal_term_node term_node ON node.vid term_node.vidWHERE (node.status 1) AND (node.type in (foto_foto))GROUP BY nid第一次是0.23s第二次是0.12s。所以我期望主查询至少应该在0.5s以下运行。这就像优化器没有在查询上运行一样。我已经在涉及的表上运行了ANALYZE和OPTIMIZE下面是EXPLAIN EXTENDED的结果。*************************** 1. row ***************************id: 1select_type: SIMPLEtable: nodetype: rangepossible_keys: vid,node_status_type,node_typekey: node_status_typekey_len: 102ref: NULLrows: 496217filtered: 100.00Extra: Using where*************************** 2. row ***************************id: 1select_type: SIMPLEtable: term_nodetype: refpossible_keys: PRIMARY,vidkey: vidkey_len: 4ref: drupal_foto.node.vidrows: 7filtered: 100.00Extra: Using where*************************** 3. row ***************************id: 1select_type: SIMPLEtable: term_datatype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: drupal_foto.term_node.tidrows: 1filtered: 100.00Extra: Using where3 rows in set, 1 warning (0.00 sec)该警告仅提供代码为1003的备注级别警告。
