on 1/2/04 12:59 PM, Adam i Agnieszka Gasiorowski FNORD wrote:
Thanks for the info. Just so I'm sure I know what I should: :)
1) You only want the day's articles. These are records in the article table and
nowhere else.
2) An article will always belong to at least one section, and may belong to multiple
sections. In either case, it should always be counted once.
3) There are several bit flags used for specific reasons that must be considered. (You
may want to consider changing these flag fields to a SET type for better readability.)
I'm pretty sure that's the important part. Now, for a line by line analysis.
> <?
> $suma = 0;
For starters, you won't need this line anymore. You'll see why in a minute.
> $pytanie = "SELECT COUNT(DISTINCT x_article.ID) AS CNT ";
This line is okay. :)
> $pytanie .= "FROM x_article ";
> $pytanie .= "LEFT JOIN x_instance ";
> $pytanie .= "ON x_article.ID = x_instance.Article ";
Since an article will always belong to at least one section (right?), you don't need
to do a left join. An equijoin will work just fine.
> $pytanie .= "LEFT JOIN x_section ";
> $pytanie .= "ON x_instance.Section = x_section.ID ";
You can also change these lines to an equijoin.
> $pytanie .= "WHERE (x_section.Status & 1) = 0 "; // not empty
If all this does is figure out if there are articles in a section, this isn't needed.
You could find that out by joining it to x_instance. (We're doing that here anyway.)
> $pytanie .= "AND (x_section.Dept = 2 OR x_section.Dept = 5) "; // Drugs,
> NeuroGroove
You should put first whichever of those is more likely to be true. If you get more
articles in your drugs category than in your NeuroGroove category, leave it. If not,
flip it. This is because most applications (and I would assume MySQL is no exception)
don't bother to continue testing logic conditions in an OR statement once they hit a
true one.
> $pytanie .= "AND (x_instance.Status & 255) = 0 "; // not hidden, etc
If this is all this flag does, it may be quicker to do an ENUM. You're forcing two
operations on it here, one for the bitmask and one for the test. Alternatively, you
could flip your flag so that 0 means hidden and 1 means visible. Then you could drop
the "=0". Ditto for the one above.
> $pytanie .= "AND UNIX_TIMESTAMP(x_article.Date) BETWEEN " . mktime(0, 0, 1,
> date('m'), date('d'), date('Y')) . " AND UNIX_TIMESTAMP(NOW()) ";
Here we reach what's probably causing a lot of the processing time. Date and time
functions are relatively processor intensive. In addition, BETWEEN can't use indexes.
Also, unless you have articles for future dates and times, you don't need to check an
upper bound.
Because datetimes are returned as strings, you can use string functions to return just
the date. Note that the date is all your really concerned about (again, unless the
time really matters to you, which it doesn't seem to.) In this case, LEFT will work
wonders.
Also, since you don't need to check for an upper bound on time, you only need to check
to see if the date is the same.
In short, you can change these lines to:
$pytanie .= "AND LEFT(x_article.Date, 10) = CURDATE()";
If you need to keep the time, just use:
$pytanie .= "AND x_article.Date >= CURDATE() AND x_article.Date <= NOW()";
If you're looking for articles that aren't for today, simply provide the appropriate
date instead of CURDATE() and NOW()
> $pytanie .= "GROUP BY x_article.ID";
You don't need a group by. You just want a count of the articles. The group by here is
doing almost the same thing as the DISTINCT above, only it's adding a lot of
processing time.
> $wynik = mysql_query($pytanie);
No problems here. :)
> while ($tmp = mysql_fetch_array($wynik))
> {
> $suma += $tmp['CNT'];
> }
With the new query, you should get your answer in one field. Those lines can change to:
$suma = mysql_result($wynik, 0, 'CNT'); // The ", 'CNT'" is actually optional.
> if ($suma)
> {
> // pretty-printing of the result
> $dzisdodano = str_pad((string)(int)$suma, 4, '0', STR_PAD_LEFT);
> }
> else $dzisdodano = '0000';
> ?>
Do you really need that else? Won't it pad a 0 with zeros?
So, here's the query in its final form, without modifying your data structure. If this
is a query still runs slowly and is run very often, you may want to consider trying to
drop the second join by moving data in your database. I'm going to rearrange the WHERE
clauses, since, depending on (or perhaps because of) how well the query optimizer
works, you should most your most restrictive clauses first.
$pytanie = "SELECT COUNT(DISTINCT x_article.ID) FROM x_article, x_instance, x_section
WHERE x_article.DATE = CURDATE() AND x_article.ID = x_instance.Article AND
(x_instance.status & 255) = 0 AND x_instance.Section = x_section.ID AND
(x_section.Dept = 2 OR x_section.Dept = 5) AND (x_section.Status & 1) = 0";
So there you go. Give it a shot and see if it runs any faster. If you need the time,
replace that first test in the WHERE clause with the one mentioned above. If this is
run often (or if it just takes a _really_ long time), put an index on the date field,
if one isn't there already. Your query as it stands has to read the entire table just
for the date field.
--
Bob
IQ2GI5SCP2
Things You Don't Hear Every Day, #'s 16 and 17:
A professor: "It's all right if you come to class high."
A(nother) professor: "I think base 16 is cool."
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
