MySQL Query Guidance Needed
paul at seamons.com
Wed Sep 27 13:43:09 MDT 2006
> READS SQL DATA
> DECLARE total DOUBLE DEFAULT 0.0;
> SELECT SUM( value ) INTO total FROM (SELECT (IFNULL( SUM(
> items.basePrice * lost.quantity * IF( items.techLevel >1, 10, 1 ) ) , 0
> ) + ships.basePrice * IF( ships.techLevel >1, 10, 1 )) / count(DISTINCT
> inv.player_id) AS value FROM kb__killmails AS mails INNER JOIN
> kb__involved AS inv ON (mails.id = inv.mail_id) LEFT JOIN kb__items_lost
> AS lost ON ( lost.mail_id = mails.id ) LEFT JOIN kb__items AS items ON (
> lost.item_id = items.typeID ) INNER JOIN kb__items AS ships ON (
> mails.ship_id = ships.typeID ) WHERE mails.id IN (SELECT inv.mail_id AS
> involved FROM kb__involved AS inv INNER JOIN kb__killmails AS kills ON
> (inv.mail_id=kills.id) WHERE inv.player_id=check_id GROUP BY
> inv.mail_id) GROUP BY mails.id) AS a;
> RETURN total;
If I was the DBA I wouldn't let that near the system. It is great that it
works but that is a maintenance nightmare. Add some whitespace and
indentation. Make it readable.
I'm not talking about how complex it is - the complexity is fine. The
readability leaves much to be desired. How many sub selects are there? Can
you tell just by glancing at it?
Indent every sub select. Gutter your SQL key words. Line up your conditions.
You'll be happy you did later.
More information about the PLUG