[thelist] SQL Query Review

[Randal Rust]

On Mon, Apr 21, 2008 at 10:51 PM, r937 <rudy at r937.com> wrote:

>  have you declared indexes on all the join columns? (note: do not do this for
>  PKs, just for FKs)

Not yet, but I figured it was something I was going to need to do.

>  also, an index on entryID would be wise

But entryID is a PK. Would I still want to do that?

>  declare column aliases for columns from separate tables that have the same name

Will that speed up the query? Or is that generally a better way to write my SQL?

>  if you don't do this, your php is messier, and if you sidestep the issue by
>  accessing the result set columns using array positions, you are asking for
>  trouble

I have always used array positions. So in the example SQL, I would
access it in PHP this way:

$entryTitle=$rs->fields[1];

>  also, use some indentation, man, i just cannot imagine how you can be
>  productive writing and debugging SQL if you don't

When I initially wrote the query, like four years ago, I probably did
do it with indentation and then removed most of the line breaks when I
was done with it.

<snip>
>         , cdc.description    AS category_descr
>         , cds.description    AS subcategory_descr
>         , cdt.description    AS topic_descr
>         , cdst.description   AS subtopic_descr
</snip>

OK, I completely understand that part of what you recommend...

>      FROM entries AS e

....but that threw me for a loop. What will that do for me?

-- 
Randal Rust
R.Squared Communications
www.r2communications.com