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


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

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);
    }
}

Building visual queries

Libre Office -> Base