[thesite] teo sidebar files modified and another SQL question

[Warden, Matt]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

thesite,

The following files have been modified and tested on teo:

/sidebar/act_averagerating.cfm        - added content.signoff=1 
                                        condition
/sidebar/act_comments.cfm             - added content.signoff=1 
                                        condition and had to 
                                        re-work the whole query 
                                        axe-ing the subqueries 
                                        and using joins to be 
                                        able to do it.
/sidebar/act_countcomments.cfm        - added signoff=1 condition 
                                        and reworked the query to
                                        highlight current hot
                                        discussions
/sidebar/dsp_countcomments.cfm        - made changes to reflect 
                                        changes in act_countcomments

What's the process for moving these things over to weo?


And rudeman, I have another question for ya. I had queries like the
following:

SELECT contentid,
         rating,
         ratings,
         contentname,
         categoryid
   FROM content 
  WHERE ratings > 5
  AND categoryid NOT IN (1,23,26,9741)
  AND signoff = 1
  ORDER BY rating DESC

And, what I ended up doing is rearranging the where clause to
something like this:

SELECT contentid,
         rating,
         ratings,
         contentname,
         categoryid
   FROM content 
  WHERE signoff = 1
  AND categoryid NOT IN (1,23,26,9741)
  AND ratings > 5
  ORDER BY rating DESC

Now, in my little head, this would cause the database to do only the
ratings > 5 comparison on the content records that had signoff of 1
and whose category idea was not 1, 23, 26, or 9741 which, again in my
little head, is a good thing.

But, I think it would depend on where the indeces are. categoryid
almost certainly has an index, no? Would it be better to have that as
the first condition? And there's no real way for me to know where the
indeces are, considering I only have access to my feo database copy
which is rather old and has some changes to it.

I'm kinda looking for a general rule here like "= is a faster
operation than >, so it's better to limit the number of records in
the > or < comparison", which would make sense to me.

Or, does the optimizer do all this anyway and I'm just fussing about
nothing?


thanks,


- --
mattwarden
mattwarden.com

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBOxFRnXgH0dUmEhrcEQIOcQCcCLbsNa5lefbW66W9UWj0DKdtKRsAn2Qe
nR5d9/oLovYChs5lKrbSxZKi
=F6MQ
-----END PGP SIGNATURE-----