[thelist] SQL Query Review
r937
rudy at r937.com
Mon Apr 21 21:51:38 CDT 2008
> 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/
More information about the thelist
mailing list