SQL



SQL Antipatterns

http://www.slideshare.net/bill[...]patterns-strike-back

  1. database design
  2. database creation
    1. Indexes are Magical
      1. Creating indexes blindly
      2. Executing non-indexable queries
      3. Rejecting indexes because of their overhead
      4. Solution: “MENTOR” your indexes
        • Measure
        • Explain
        • Nominate
        • Test
        • Optimize
        • Repair
  3. query
  4. application

Measure

  • Profile your application.
  • Focus on the most costly SQL queries
    • Longest-running
    • Most frequently run
    • Blockers, lockers, and deadlocks.

Performance

Indexes

  1. Ensure indexes on columns used in WHERE, ON, GROUP BY clauses
  2. Always ensure JOIN conditions are indexed (and have identical data types)
  3. Be careful of the column order
  4. If you don't pick a primary key (bad idea!), one will be created for you -> And, you have no control over the key

An Iterating Query Example


<?php
 
if (isset($_GET['ids'])) 
{
    foreach($_GET['ids'] as $id)
    {
        $rs = mysql_query('SELECT * FROM my_table WHERE my_id = ' . (int) $id);
        $row = mysql_fetch_assoc($rs);
        print_r($row);
    }
}

Fixing The Iterating Query Example

<?php
 
if (isset($_GET['ids']))
{
    $ids = array_map('intval', $_GET['ids']);
    $ids = implode(',', $ids);
    $rs = mysql_query('SELECT * FROM my_table WHERE my_id IN (' . $ids . ')');
    
    while($row = mysql_fetch_assoc($rs))
    {
        print_r($row);
    }
}

Check for SQL STRICT mode violations

  1. Using GROUP BY and selecting an ambiguous column
  2. Inserting the non standard zero date into a datetime column
  3. Inserting a 20 character string into a 10 character column
  4. Division by zero
  5. Inserting a negative value into an unsigned column

so far

  1. #1406 - Data too long for column 'description' at row 1
    1. set HTML maxlength="DB_FIELDSIZE" for all (VAR)CHAR form field
      • suggested (JS hint - might differ in some cases - smaller, e.g. meta description 160, meta title 60) + database field size (mandatory enforcement)
      • JS hint: You have <strong>60</strong> characters left
    2. set PHP length check before passing to INPUT / UPDATE
  2. #1055 - 'dev.g.group_name' isn't in GROUP BY

SELECT
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
((`SUM_ERRORS` > 0) OR (`SUM_WARNINGS` > 0))
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

Building visual queries

Libre Office -> Base