===== MySQL Style Guide =====

== General Guidelines ==

CAPITALIZE ALL keywords
Whitespace and tabs help others read your queries

Naming Conventions

Table names are singular nouns with hierarchical names (fee_setting_seller)

Foreign Key:
<code>FK__foreign_key_table__primary_key_table</code>

Unique Constraint:
<code>UX__table_name__column_name</code>

Index:
<code>IX__table_name__column_name</code>

Simple Queries

Simple query example:

<code>
SELECT p.id, i.price
FROM product p
LEFT JOIN inventory i ON p.id = i.id
WHERE p.name = 'something'
GROUP BY p.id
HAVING i.price > 0
ORDER BY p.name ASC
LIMIT 50;
</code>

For very short queries, a one-liner will do:

<code>SELECT * FROM product ORDER BY name ASC;</code>

Complex Queries

Complex query example:

<code>
SELECT
   p.id,
   p.name,
   i.price,
   SUM(i.stock) stock_level,
   MAX(s.eta) eta

FROM products p
   LEFT JOIN inventory i ON p.id = i.id
   LEFT JOIN shipment s ON i.shipment = s.id

WHERE
   p.name = 'something'
   AND s.delivered = 0

GROUP BY p.id
ORDER BY p.name ASC

LIMIT 50;
</code>

Note the whitespace and indentation. This format is incredibly effective for large or complex queries, especially INSERTs, UPDATES, and REPLACEs.

Subqueries

Use indentation to set off subqueries:

<code>
SELECT p.id, p.name
FROM products p
WHERE p.id IN(
   SELECT DISTINCT product
   FROM transaction
   WHERE status = 2
)
ORDER BY p.name ASC
LIMIT 50;

UNIONS</code>

Clearly separate queries in a UNION by a newline above and below the UNION keyword:

<code>
SELECT id, name
FROM user
WHERE timestamp > '2012-01-12'

UNION

SELECT id, CONCAT('first_name', ' ', 'last_name') name
FROM transaction
WHERE timestamp > '2012-01-12'
</code>
Analytics

I understand that for analytics queries may become ridiculous. Let's try to avoid such complicated DB structures or queries in our application but here's an example of how I formatted a long query dealing with subselects and multiple databases when I didn't have the luxury of designing the databases myself:

<code>
SET @num := 0, @zipcode := '';

SELECT
   u.id,
   u.last_name,
   u.first_name,
   u.email user_email,
   u.password,
   u.salt,
   u.type,
   u.registration_date,
   u.last_login_date,
   u.active user_status,
   uma.amount user_allowance,
   c.companyId,
   c.name company_name,
   c.city company_city,
   c.zipcode company_zip,
   c.size company_size,
   c.active company_status,
   tvz.vendor1,
   tvz.vendor2,
   tvz.vendor3,
   tvz.vendor4,
   tvz.vendor5,
   a.balance,
   a.points,
   (a.points + a.balance) total_budget,
   ma.census_statistical_area metro_area
FROM
   user u
LEFT JOIN
   user_max_allowance uma ON uma.id = u.id
LEFT JOIN
   company c ON c.id = u.company_id
LEFT JOIN (
   SELECT
        v.zipcode,
        GROUP_CONCAT(if(v.row_num = 1, v.name, NULL)) vendor1,
        GROUP_CONCAT(if(v.row_num = 2, v.name, NULL)) vendor2,
        GROUP_CONCAT(if(v.row_num = 3, v.name, NULL)) vendor3,
        GROUP_CONCAT(if(v.row_num = 4, v.name, NULL)) vendor4,
        GROUP_CONCAT(if(v.row_num = 5, v.name, NULL)) vendor5
   FROM (
        SELECT
             c.name,
             p.zipcode,
             p.popularity,
             p.row_num
        FROM (
             SELECT
                  vendor_id,
                  zipcode,
                  popularity,
                  @num := if(@zipcode = zipcode, @num + 1, 1) row_num,
                  @zipcode := zipcode dummy
             FROM
                  perk
             WHERE
                  state = '1'
                  AND zipcode <> ''
                  AND zipcode IS NOT NULL
             ORDER BY
                  zipcode ASC,
                  popularity DESC
        ) p
        LEFT JOIN
             company c ON c.id = p.vendor_id
        WHERE
             p.row_num <= 5
   ) v
   GROUP BY
     v.zipcode
) tvz ON tvz.zipcode = c.zipcode
LEFT JOIN (
   SELECT
       user_id,  
       GROUP_CONCAT(if(type = 1, credit_balance, NULL)) balance,
       GROUP_CONCAT(if(type = 2, credit_balance, NULL)) points
   FROM
       payment.account
   WHERE
       type = 1
       OR type = 2
   GROUP BY
       user_id
) a ON a.user_id = u.id
LEFT JOIN
   analytics.metro_areas ma ON ma.zip = c.zipcode
WHERE
   u.type = 1
   OR u.type = 2;
</code>