Generally speaking, bit masks incur performance penalties in return for space gains... but storage is cheap and time isn't. This penalty is worse for databases. The general rule is that bit-mask fields and databases are a bad combination. If you break bit zero out of x_section.Status into say x_section.isEmpty (defined as a tinyint or char(1) if you are really worried about space), then add an index on x_section.isEmpty, then you'll get the performance gain. Repeat as required with x_instance.Status.
If breaking out the bit masks is going to be really painful, then consider getting a result set without the mask criteria in the query, make sure the remaining fields in the WHERE are indexed, then filter out the results you want in the script. This depends on which percentage of the result set you eliminate with those masks... if the result set is 10% bigger, then this works, if the result set is 10,000% bigger then take the pain and go break up the bit-mask fields.
At 2004/01/02 12:59, Adam i Agnieszka Gasiorowski FNORD wrote:
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
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
