MySQL Query Guidance Needed

Matthew Walker rorith at kydance.net
Wed Sep 27 13:45:46 MDT 2006


That's straight out of phpMyAdmin's export function. Just wanted to grab
it fast and post it, since I don't have my dev files for that at work. :)
Sorry for the uglyness.

On Wed, September 27, 2006 1:43 pm, Paul Seamons wrote:
>>     READS SQL DATA
>> BEGIN
>> 		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;
>> 	END
>
> 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.
>
> Paul
>
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */
>


-- 
Matthew Walker
Kydance Hosting & Consulting
LAMP Specialist

-- 
Matthew Walker
Kydance Hosting & Consulting
LAMP Specialist



More information about the PLUG mailing list