From 1d3fc9005f00927c19c291f33b5bfffdd81cad6e Mon Sep 17 00:00:00 2001 From: rvelices Date: Thu, 8 Sep 2011 18:22:27 +0000 Subject: - reoptimize queries for MySql - week starts on monday/sunday applies also to weekly calendar git-svn-id: http://piwigo.org/svn/trunk@12118 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/calendar_base.class.php | 4 +--- include/calendar_monthly.class.php | 37 ++++++++++++++----------------------- include/calendar_weekly.class.php | 13 ++++++++----- include/functions_calendar.inc.php | 33 --------------------------------- include/functions_tag.inc.php | 10 +++++++--- 5 files changed, 30 insertions(+), 67 deletions(-) (limited to 'include') diff --git a/include/calendar_base.class.php b/include/calendar_base.class.php index 5f85c914f..a64e20fe9 100644 --- a/include/calendar_base.class.php +++ b/include/calendar_base.class.php @@ -220,7 +220,6 @@ $this->get_date_where($level).' ;'; $level_items = simple_hash_from_query($query, 'period', 'nb_images'); - $level_items = array_to_int($level_items); if ( count($level_items)==1 and count($page['chronology_date'])calendar_levels)-1) @@ -292,7 +291,6 @@ GROUP BY period'; $current = implode('-', $page['chronology_date'] ); $upper_items = array_from_query( $query, 'period'); - $upper_items = array_to_int($upper_items); usort($upper_items, 'version_compare'); $upper_items_rank = array_flip($upper_items); @@ -347,4 +345,4 @@ GROUP BY period'; } } } -?> +?> \ No newline at end of file diff --git a/include/calendar_monthly.class.php b/include/calendar_monthly.class.php index 4b176f85f..2c14088c9 100644 --- a/include/calendar_monthly.class.php +++ b/include/calendar_monthly.class.php @@ -224,15 +224,13 @@ function build_global_calendar(&$tpl_var) assert( count($page['chronology_date']) == 0 ); $query=' -SELECT '.pwg_db_get_date_YYYYMM($this->date_field).' as period,' - .pwg_db_get_year($this->date_field).' as year, ' - .pwg_db_get_month($this->date_field).' as month, - count(distinct id) as count'; +SELECT '.pwg_db_get_date_YYYYMM($this->date_field).' as period, + COUNT(distinct id) as count'; $query.= $this->inner_sql; $query.= $this->get_date_where(); $query.= ' - GROUP BY period, year, month - ORDER BY year DESC, month ASC'; + GROUP BY period + ORDER BY '.pwg_db_get_year($this->date_field).' DESC, '.pwg_db_get_month($this->date_field).' ASC'; $result = pwg_query($query); $items=array(); @@ -368,11 +366,6 @@ SELECT id, file,tn_ext,path, width, height, '.pwg_db_get_dayofweek($this->date_f $items[$day]['width'] = $row['width']; $items[$day]['height'] = $row['height']; $items[$day]['dow'] = $row['dow']; - - if ('sunday' == $conf['week_starts_on']) - { - $items[$day]['dow']++; - } } if ( !empty($items) @@ -389,21 +382,19 @@ SELECT id, file,tn_ext,path, width, height, '.pwg_db_get_dayofweek($this->date_f //first_day_dow = week day corresponding to the first day of this month $wday_labels = $lang['day']; - // BEGIN - pass now in week starting Monday - if ($first_day_dow==0) - { - $first_day_dow = 6; - } - else - { - $first_day_dow -= 1; - } - if ('monday' == $conf['week_starts_on']) { + if ($first_day_dow==0) + { + $first_day_dow = 6; + } + else + { + $first_day_dow -= 1; + } + array_push( $wday_labels, array_shift($wday_labels) ); } - // END - pass now in week starting Monday $cell_width = $conf['calendar_month_cell_width']; $cell_height = $conf['calendar_month_cell_height']; @@ -528,4 +519,4 @@ SELECT id, file,tn_ext,path, width, height, '.pwg_db_get_dayofweek($this->date_f } } -?> +?> \ No newline at end of file diff --git a/include/calendar_weekly.class.php b/include/calendar_weekly.class.php index 8ebcf0174..146bb1788 100644 --- a/include/calendar_weekly.class.php +++ b/include/calendar_weekly.class.php @@ -40,7 +40,7 @@ class Calendar extends CalendarBase function initialize($inner_sql) { parent::initialize($inner_sql); - global $lang; + global $lang, $conf; $week_no_labels=array(); for ($i=1; $i<=53; $i++) { @@ -64,10 +64,13 @@ class Calendar extends CalendarBase ); //Comment next lines for week starting on Sunday or if MySQL version<4.0.17 //WEEK(date,5) = "0-53 - Week 1=the first week with a Monday in this year" - $this->calendar_levels[CWEEK]['sql'] = pwg_db_get_week($this->date_field, 5).'+1'; - $this->calendar_levels[CDAY]['sql'] = pwg_db_get_weekday($this->date_field); - array_push( $this->calendar_levels[CDAY]['labels'], - array_shift( $this->calendar_levels[CDAY]['labels'] ) ); + if ('monday' == $conf['week_starts_on']) + { + $this->calendar_levels[CWEEK]['sql'] = pwg_db_get_week($this->date_field, 5).'+1'; + $this->calendar_levels[CDAY]['sql'] = pwg_db_get_weekday($this->date_field); + array_push( $this->calendar_levels[CDAY]['labels'], + array_shift( $this->calendar_levels[CDAY]['labels'] ) ); + } } /** diff --git a/include/functions_calendar.inc.php b/include/functions_calendar.inc.php index cbd3eec25..904514cd1 100644 --- a/include/functions_calendar.inc.php +++ b/include/functions_calendar.inc.php @@ -289,37 +289,4 @@ WHERE id IN (' . implode(',',$page['items']) .')'; } pwg_debug('end initialize_calendar'); } - -function array_to_int($array) -{ - $result = array(); - foreach ($array as $k => $v) - { - if ((int)$k==$k) - { - $key = (int) $k; - } - else - { - $key = $k; - } - if (is_array($v)) - { - $result[$key] = array_to_int($v); - } - else - { - if (is_int($v)) - { - $value = (int) $v; - } - else - { - $value = $v; - } - $result[$key] = $value; - } - } - return $result; -} ?> \ No newline at end of file diff --git a/include/functions_tag.inc.php b/include/functions_tag.inc.php index 028fd766c..9f93115fd 100644 --- a/include/functions_tag.inc.php +++ b/include/functions_tag.inc.php @@ -228,13 +228,17 @@ SELECT t.*, count(*) AS counter AND tag_id NOT IN ('.implode(',', $excluded_tag_ids).')'; } $query .=' - GROUP BY tag_id, t.id, t.name, t.url_name'; + GROUP BY t.id + ORDER BY '; if ($max_tags>0) { - $query .= ' - ORDER BY counter DESC + $query .= 'counter DESC LIMIT '.$max_tags; } + else + { + $query .= 'NULL'; + } $result = pwg_query($query); $tags = array(); -- cgit v1.2.3