Bob Terrell wrote:
> 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: :)
Thanks for your answer, very helpful!
> > <?
> > $suma = 0;
>
> For starters, you won't need this line anymore. You'll see why in a minute.
Can't wait! :8]
> > $pytanie = "SELECT COUNT(DISTINCT x_article.ID) AS CNT ";
>
> This line is okay. :)
Cool. I'm not _that_ stupid, then ;8].
> > $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.
You are right.
> > $pytanie .= "LEFT JOIN x_section ";
> > $pytanie .= "ON x_instance.Section = x_section.ID ";
>
> You can also change these lines to an equijoin.
Sure. But isn't LEFT JOIN specially optimized
in MySQL, or it doesn't matter in this case? NATURAL
JOIN if else.
> > $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.)
Great! One less bit mask test! :8]. An improvment!
> > $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.
This one will not be changed, Drugs is MUCH bigger
than NeuroGroove (and updated more often, people rarely
write good experience reports).
> > $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.
EXCELENT IDEA!
So it will be 'AND (x_instance.Status & 0)' now :8]. Yea!
Should give me TRUE if there are not bits set, right? Ha ha!
One less test!
> > $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()";
Wow!
> If you're looking for articles that aren't for today, simply provide the appropriate
> date instead of CURDATE() and NOW()
Fortunately, I don't need to do that right 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.
Thank you! I'll test it, just to be sure.
> > $wynik = mysql_query($pytanie);
>
> No problems here. :)
LOL.
> > 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.
Of course.
> > 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?
Yes, that is what I had in mind. No change here.
> 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.
Thanks for the tip, I didn't consider it! Will do from now!
You are very helpful.
> $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.
Thank you, thank you! :8].
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
