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.Arti cle ";
$pytanie .= "LEFT JOIN x_section ";
$pytanie .= "ON x_instance.Sect ion = x_section.ID ";
$pytanie .= "WHERE (x_section.Stat us & 1) = 0 "; // not empty
$pytanie .= "AND (x_section.Dept = 2 OR x_section.Dept = 5) "; // Drugs, NeuroGroove
$pytanie .= "AND (x_instance.Sta tus & 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($py tanie);
while ($tmp = mysql_fetch_arr ay($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@hyperre al.pl | |
| 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.sek s.moderowana
http://hyperreal.info { iWanToDie } WiNoNa ) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kszta³ty... http://www.opera.com 007
Comment