I need help width formulating the most effective (in terms of processing time) SQL query to count all the "new" documents in the repository, where "new" is defined as "from 00:00:01 up to 23:59:59 today". My current query does not give me satisfactory results, it creates a visible delay in rendering of the main page of one of the departments (Drugs) :8[[[ (at least I, for now, think it's the culprit). It's for the <url: https://hyperreal.info > site, see for yourself, notice the delay <url: https://hyperreal.info/drugs/go.to/index >. Currently I ask MySQL to (offending PHP fragment follows, I hope it is self- explanatory). <? $suma = 0; $pytanie = "SELECT COUNT(DISTINCT x_article.ID) AS CNT "; $pytanie .= "FROM x_article "; $pytanie .= "LEFT JOIN x_instance "; $pytanie .= "ON x_article.ID = x_instance.Article "; $pytanie .= "LEFT JOIN x_section "; $pytanie .= "ON x_instance.Section = x_section.ID "; $pytanie .= "WHERE (x_section.Status & 1) = 0 "; // not empty $pytanie .= "AND (x_section.Dept = 2 OR x_section.Dept = 5) "; // Drugs, NeuroGroove $pytanie .= "AND (x_instance.Status & 255) = 0 "; // not hidden, etc $pytanie .= "AND UNIX_TIMESTAMP(x_article.Date) BETWEEN " . mktime(0, 0, 1, date('m'), date('d'), date('Y')) . " AND UNIX_TIMESTAMP(NOW()) "; $pytanie .= "GROUP BY x_article.ID"; $wynik = mysql_query($pytanie); while ($tmp = mysql_fetch_array($wynik)) { $suma += $tmp['CNT']; } if ($suma) { // pretty-printing of the result $dzisdodano = str_pad((string)(int)$suma, 4, '0', STR_PAD_LEFT); } else $dzisdodano = '0000'; ?> The table layout is as follows: mysql> DESC x_article; +-------------+------------------+------+-----+----------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+----------------------+----------------+ | ID | int(10) unsigned | | PRI | NULL | auto_increment | | Name | varchar(255) | YES | MUL | NULL | | | Description | varchar(255) | YES | | NULL | | | Keywords | varchar(255) | YES | | NULL | | | Content | mediumtext | | | | | | Date | datetime | | | 2001-01-01 00:00:00 | | | Author | varchar(100) | | | unknown@xxxxxxxxxxxx | | | Feedback | varchar(100) | YES | | NULL | | | Size | int(32) | YES | | NULL | | | Words | int(32) | YES | | NULL | | | Images | int(32) | YES | | NULL | | +-------------+------------------+------+-----+----------------------+----------------+ mysql> DESC x_instance; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | Article | mediumint(9) | | MUL | 0 | | | Section | mediumint(9) | | MUL | 0 | | | Priority | tinyint(4) | | | 0 | | | Status | int(16) unsigned | | | 0 | | +----------+------------------+------+-----+---------+-------+ mysql> DESC x_section; +----------+----------------------+------+-----+---------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------------+----------------+ | ID | mediumint(9) | | PRI | NULL | auto_increment | | Name | varchar(100) | | MUL | | | | Parent | mediumint(9) | | MUL | 0 | | | Dept | smallint(6) | | MUL | 0 | | | Priority | tinyint(4) | | | 3 | | | Keywords | varchar(255) | YES | | NULL | | | Sorting | varchar(255) | | | Priority DESC | | | OrderBy | varchar(255) | YES | | NULL | | | SplitAt | smallint(5) unsigned | | | 25 | | | Status | int(16) unsigned | | | 0 | | +----------+----------------------+------+-----+---------------+----------------+ Tell me if you need any additional information. Thank you for all your help. MySQL version is 4.0.17, PLD Linux Distribution MySQL RPM. -- Seks, seksić, seksolatki... news:pl.soc.seks.moderowana http://hyperreal.info { iWanToDie } WiNoNa ) ( http://szatanowskie-ladacznice.0-700.pl foReVeR( * ) Poznaj jej zwiewne kształty... http://www.opera.com 007 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
