{{tree}}
{{toc}}
===SQL Antipatterns===
http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
1. database design
2. database creation
3. Indexes are Magical
4. Creating indexes blindly
1. Executing non-indexable queries
1. Rejecting indexes because of their overhead
2. 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
1. Always ensure JOIN conditions are indexed (and have identical data types)
1. Be careful of the column order
1. 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===
%%(hl php)
<?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
%%(hl php)
<?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 ((/Dev/Guidelines/SQL/SQLmodes SQL STRICT mode)) violations===
1. Using GROUP BY and selecting an ambiguous column
1. Inserting the non standard zero date into a datetime column
1. Inserting a 20 character string into a 10 character column
1. Division by zero
1. Inserting a negative value into an unsigned column
so far
1. #1406 - Data too long for column 'description' at row 1
2. 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
1. set PHP length check before passing to INPUT / UPDATE
2. #1055 - 'dev.g.group_name' isn't in GROUP BY
%%(hl sql)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