Jeremy Peterson wrote:
> Could you repost your original message (I deleted it already, sorry.) and
> more details about your database tables even some sample data. I'll try to
> make more sense of the query you are performing and help you along.
>
> In general it would be better to eliminate the joins. But there are other
> reasons to use the joins as you are doing, it just depends. Sometimes
> doing your distinct first then processing that data in an array will be
> faster. That is as long as you don't have a million records it needs to
> process.
>
> I'll see what I can do,
To put it short, this just takes to long
to process. I need to make it faster. Relevant
fragment below. What I want is a sum of all
"new", instantiated (visible) articles in all non
hidden sections, where "new" is defined as
"today, from 00:00:01 to 23:59:59". As you can
see I count in departments "2" and "5", because
other would give false results(the "3" department
shares a lot of articles with the "2" department
and if there is an article in the "3" department
it is always in "2" too). First I need to know
if the article is instantiated, so I JOIN with
x_instance. From the x_instance table I get also
the "state" of the article. Then I need to know the
department plus is the section I am querying visible at
all (some aren't), so I JOIN on x_section. Then
I add all the counts over all the sections and
pretty-print a sum. If you need some diagnostic
information, please tell me what you need (results
of EXPLAIN? SHOW?). IIRC, all the fields I JOIN on
are indexed (BTREE).
> <?
> $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 | |
> +----------+----------------------+------+-----+---------------+----------------+
--
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
