[eluser]popovich[/eluser]
..., the second excerpt is the abnormal behavior:
Code:
DEBUG - 2010-10-13 18:03:17 --> Config Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Hooks Class Initialized
DEBUG - 2010-10-13 18:03:17 --> URI Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Router Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Output Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Input Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Global POST and COOKIE data sanitized
DEBUG - 2010-10-13 18:03:17 --> Language Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Loader Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: url_helper
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: cookie_helper
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: markdown_helper
DEBUG - 2010-10-13 18:03:17 --> Database Driver Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Session Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: string_helper
DEBUG - 2010-10-13 18:03:17 --> Session routines successfully run
DEBUG - 2010-10-13 18:03:17 --> Controller Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Model Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Model Class Initialized
DEBUG - 2010-10-13 18:03:17 --> User Agent Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Model Class Initialized
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: spec_helper
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: typography_helper
DEBUG - 2010-10-13 18:03:17 --> Helper loaded: modules_helper
DEBUG - 2010-10-13 18:03:17 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/modules/mv_press_viewer.php
DEBUG - 2010-10-13 18:03:18 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/modules/mv_latest_news.php
DEBUG - 2010-10-13 18:03:18 --> File loaded: J:\ATLAS\**WWW_New/system/application/views/modules/mv_geo_list.php
DEBUG - 2010-10-13 18:03:18 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/meta.php
DEBUG - 2010-10-13 18:03:18 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/stylesheets.php
DEBUG - 2010-10-13 18:03:18 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/scripts.php
DEBUG - 2010-10-13 18:03:19 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/custom_jquery.php
DEBUG - 2010-10-13 18:03:20 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/analytics.php
DEBUG - 2010-10-13 18:03:20 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/logo.php
DEBUG - 2010-10-13 18:03:21 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/navi.php
DEBUG - 2010-10-13 18:03:21 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/navi.php
DEBUG - 2010-10-13 18:03:23 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/navi.php
DEBUG - 2010-10-13 18:03:23 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/partials/navi.php
DEBUG - 2010-10-13 18:03:23 --> File loaded: J:\ATLAS\**\WWW_New/system/application/views/layouts/two_columns_view.php
DEBUG - 2010-10-13 18:03:23 --> Final output sent to browser
DEBUG - 2010-10-13 18:03:23 --> Total execution time: 5.2286
Here are the two mysql statements, the second one being the new one. Switching between the old and the new statements now doesn't affect the execution time at all.
Code:
$q = "SELECT c.do_trigger, l.label
FROM pos_country_label as l
LEFT JOIN pos_country as c
ON l.country_id = c.id
WHERE l.lang_id = ".$this->CI->GLO['user_lang_id']."
AND c.published = 'yes'
ORDER BY l.label ASC;";
( the new statement )
Code:
$q = "SELECT c.do_trigger,
l.label,
( SELECT COUNT(*) FROM pos_details pos WHERE pos.published = 'yes' AND pos.city_id = pc.id ) AS pos_on,
( SELECT COUNT(*) FROM pos_city pc WHERE pc.published = 'yes' AND pc.country_id = c.id) AS city_on
FROM pos_country_label AS l,
pos_country AS c,
pos_city AS pc
WHERE l.lang_id =".$this->CI->GLO['user_lang_id']."
AND c.published = 'yes'
AND l.country_id = c.id
AND pc.country_id = c.id
GROUP BY c.id
ORDER BY l.label ASC
LIMIT 0,30;";
OK, I have "optimize"d and"analyze"d couple of tables, added two index columns to the two of the tables and later removed the columns. The speed just went down since running the updated sql-statement for the first time and never came back. Every other script is running wonderfully on the same Windows box (PMA, the CMS' backend, whatever) and the Linux server also doesn't have any problems.
What could be the cause?