[thelist] SQL Query Review

[r937]

> I have this query that fetches a record for an online encyclopedia.
> Does anyone see any efficiencies that I could make?
> This is a MySQL database, and we use PHP.

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

also, an index on entryID would be wise

you asked for efficiencies, and here's one:  declare column aliases for
columns from separate tables that have the same name

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

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

so change this --

select e.entryID, e.entryTitle, e.category, e.subcategory, e.narrative,
e.firstName, e.lastName, e.middleName, e.topic, e.subtopic, cdc.description,
cds.description, cdt.description, cdst.description, e.linkTitle,
e.entryStatus, e.suffix, e.nickname, e.previewText, e.altFirstName,
e.altMiddleName, e.altLastName, e.altSuffix, e.altNativeName, e.dateCreated,
e.dateChanged, e.alternateLink, e.latitude, e.longitude from entries e

to this --

   SELECT e.entryID
        , e.entryTitle
        , e.category
        , e.subcategory
        , e.narrative
        , e.firstName
        , e.lastName
        , e.middleName
        , e.topic
        , e.subtopic
        , cdc.description    AS category_descr
        , cds.description    AS subcategory_descr
        , cdt.description    AS topic_descr
        , cdst.description   AS subtopic_descr
        , e.linkTitle
        , e.entryStatus
        , e.suffix
        , e.nickname
        , e.previewText
        , e.altFirstName
        , e.altMiddleName
        , e.altLastName
        , e.altSuffix
        , e.altNativeName
        , e.dateCreated
        , e.dateChanged
        , e.alternateLink
        , e.latitude
        , e.longitude
     FROM entries AS e
   ...

rudy
http://r937.com/