CHAR_LENGTH slowering query 2

[Sleidia]

Hi,

I have this query :

SELECT

tab_nav.nav_zone_ID, tab_nav.nav_page_ID, tab_nav.nav_name,
tab_nav.nav_link_text_fr AS nav_link_text,
tab_nav.nav_link_tip_fr AS nav_link_tip, tab_nav.nav_link_pic,
tab_nav.nav_link_url_fr AS nav_link_url, tab_nav.nav_link_win,
tab_nav.nav_link_innercode_li, tab_nav.nav_link_innercode_ahref,
tab_nav.nav_option_ispage,

(SELECT IF(nav_name <> '','',tab_pages.page_path_fr)) AS page_path,
(
SELECT
CHAR_LENGTH(GROUP_CONCAT(content_text))
FROM
def_texts_content
WHERE
content_page_ID = tab_pages.page_ID
AND
content_lang LIKE 'fr'
) AS content_length

FROM
def_zones_nav AS tab_nav, def_pages AS tab_pages

WHERE
(tab_nav.nav_page_ID = tab_pages.page_ID
OR
tab_nav.nav_name NOT LIKE ''
)
AND
tab_nav.nav_zone_ID IN (37,12,15,16,29,31,1,7)
AND
tab_pages.lock_view NOT LIKE 'yes'

GROUP BY
tab_nav.nav_zone_ID, tab_nav.table_ID

ORDER BY
tab_nav.nav_zone_ID ASC, tab_nav.nav_link_order, nav_link_text
ASC

It's painfully slow but it runs normally when I remove this part :

(
SELECT
CHAR_LENGTH(GROUP_CONCAT(content_text))
FROM
def_texts_content
WHERE
content_page_ID = tab_pages.page_ID
AND
content_lang LIKE 'fr'
) AS content_length

The question is : how do I speed up the query without throwing away the feature consisting in checking if a page has some content in it?

Thanks for the help!

 

[towerbase]

Poor performance is frequently the result of not indexing the appropriate columns. Can you show us the results of

SHOW CREATE TABLE <tablename>

for the various tables?
You seem to use LIKE when = would be more appropriate (as no wild characters are present). However, I doubt that correcting this would make much of a performance improvement.

Andrew
Hampshire, UK

 

[Sleidia]

Hi towerbase
Thanks for your help

I hope you're right about the indexing optimization.

Here are the tables structures.

CREATE TABLE `def_texts_content` (
  `content_ID` smallint(6) NOT NULL auto_increment,
  `content_page_ID` varchar(4) default NULL,
  `content_zone_ID` varchar(4) NOT NULL,
  `content_lang` varchar(2) NOT NULL,
  `content_country` tinytext NOT NULL,
  `content_text` text NOT NULL,
  `content_text_tmp` text NOT NULL,
  `content_edit_date` date NOT NULL default '0000-00-00',
  `content_edit_admin_ID` tinytext NOT NULL,
  `content_pub_date` date NOT NULL default '0000-00-00',
  `content_pub_admin_ID` tinytext NOT NULL,
  UNIQUE KEY `table_ID` (`content_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2338 DEFAULT CHARSET=utf8

CREATE TABLE `def_zones_nav` (
  `table_ID` smallint(6) NOT NULL,
  `nav_zone_ID` smallint(6) NOT NULL,
  `nav_page_ID` tinytext NOT NULL,
  `nav_name` tinytext NOT NULL,
  `nav_link_text_fr` tinytext NOT NULL,
  `nav_link_tip_fr` mediumtext NOT NULL,
  `nav_link_url_fr` tinytext NOT NULL,
  `nav_link_text_en` tinytext NOT NULL,
  `nav_link_tip_en` mediumtext NOT NULL,
  `nav_link_url_en` tinytext NOT NULL,
  `nav_link_text_ru` tinytext NOT NULL,
  `nav_link_tip_ru` mediumtext NOT NULL,
  `nav_link_url_ru` tinytext NOT NULL,
  `nav_link_text_ro` tinytext NOT NULL,
  `nav_link_tip_ro` mediumtext NOT NULL,
  `nav_link_url_ro` tinytext NOT NULL,
  `nav_link_text_de` tinytext NOT NULL,
  `nav_link_tip_de` mediumtext NOT NULL,
  `nav_link_url_de` tinytext NOT NULL,
  `nav_link_text_hu` tinytext NOT NULL,
  `nav_link_tip_hu` mediumtext NOT NULL,
  `nav_link_url_hu` tinytext NOT NULL,
  `nav_link_text_it` tinytext NOT NULL,
  `nav_link_tip_it` mediumtext NOT NULL,
  `nav_link_url_it` tinytext NOT NULL,
  `nav_link_text_es` tinytext NOT NULL,
  `nav_link_tip_es` mediumtext NOT NULL,
  `nav_link_url_es` tinytext NOT NULL,
  `nav_link_text_pt` tinytext NOT NULL,
  `nav_link_tip_pt` mediumtext NOT NULL,
  `nav_link_url_pt` tinytext NOT NULL,
  `nav_link_pic` tinytext NOT NULL,
  `nav_link_innercode_ahref` mediumtext NOT NULL,
  `nav_link_innercode_li` mediumtext NOT NULL,
  `nav_link_order` tinyint(4) NOT NULL default '0',
  `nav_link_win` enum('yes','') NOT NULL,
  `nav_option_ispage` enum('current_only','current_parent','') NOT NULL,
  UNIQUE KEY `table_ID` (`table_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `def_pages` (
  `page_ID` bigint(20) NOT NULL default '0',
  `page_parent_ID` bigint(20) default NULL,
  `page_name_fr` tinytext NOT NULL,
  `page_dir_fr` tinytext NOT NULL,
  `page_path_fr` tinytext NOT NULL,
  `page_name_en` tinytext NOT NULL,
  `page_dir_en` tinytext NOT NULL,
  `page_path_en` tinytext NOT NULL,
  `page_name_ru` tinytext NOT NULL,
  `page_dir_ru` tinytext NOT NULL,
  `page_path_ru` tinytext NOT NULL,
  `page_name_ro` tinytext NOT NULL,
  `page_dir_ro` tinytext NOT NULL,
  `page_path_ro` tinytext NOT NULL,
  `page_name_it` tinytext NOT NULL,
  `page_dir_it` tinytext NOT NULL,
  `page_path_it` tinytext NOT NULL,
  `page_name_de` tinytext NOT NULL,
  `page_dir_de` tinytext NOT NULL,
  `page_path_de` tinytext NOT NULL,
  `page_name_es` tinytext NOT NULL,
  `page_dir_es` tinytext NOT NULL,
  `page_path_es` tinytext NOT NULL,
  `page_name_pt` tinytext NOT NULL,
  `page_dir_pt` tinytext NOT NULL,
  `page_path_pt` tinytext NOT NULL,
  `page_name_hu` tinytext NOT NULL,
  `page_dir_hu` tinytext NOT NULL,
  `page_path_hu` tinytext NOT NULL,
  `page_order` int(11) NOT NULL,
  `lock_nav` set('yes','') NOT NULL default '',
  `lock_view` set('yes','') NOT NULL default 'yes',
  `lock_del` set('yes','') NOT NULL default '',
  UNIQUE KEY `page_ID` (`page_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

[KarveR]

Just out of curiosity, what do your tables indexes show, I had a table recently where the indexes were showing as "NULL" and a quick anaylze table <tablename> fixed these and corrected my speed issue.
Alternatively I guess you could set up a char_length field and populate it from a trigger on insert so that you don't get the delay when querying forevermore.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Sleidia]

Hi KarveR

I have these below.
Basically I have only one index per table, the table ID.

Thanks for trying to help !

TABLE      Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT
---------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
def_pages           0  page_ID              1  page_ID      A                   65    (NULL)  (NULL)          BTREE

TABLE              Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT
-----------------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
def_texts_content           0  table_ID             1  content_ID   A                 1205    (NULL)  (NULL)          BTREE

TABLE          Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT
-------------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
def_zones_nav           0  table_ID             1  table_ID     A                   51    (NULL)  (NULL)          BTREE