From: Adam i Agnieszka Gasiorowski FNORD (agquarxvenus.ci.uw.edu.pl)
Date: Sun Jan 04 2004 - 03:05:19 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]