Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Effective Software Development with MySQL

Effective Software Development with MySQL

While the MySQL RDBMS is essential for storing your information, how your application interacts and with applicable business logic is what makes each company is unique.

Some key points in this presentation include.

- Integrating MySQL with common programming languages
- The various developer tools that work well with MySQL
- The importance on developing standards and good SQL practices
- Common mistakes developers and frameworks make, and how to avoid them.
- What causes the most time for development and support time.
- How to invest time in quality and performance during development

We will review some of the important changes between versions including new features and compatibility differences. A great example is the impact of the new sql_mode default in Version 5.6.

From this presentation, we will create a checklist of what developers need to know and do for creating great software that lasts.

Ronald Bradford

August 09, 2015
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

Transcript

  1. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    Effective Software
    Development with
    MySQL
    1
    Ronald Bradford
    August 2015
    Sunday, August 23, 15

    View full-size slide

  2. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective Software Development with MySQL
    25+ years Experience
    2
    “The greatest performance
    overhead in MySQL systems today
    is the lack of SQL skills and general
    development skills by the software
    creators.”
    Ronald Bradford
    August 2014
    Sunday, August 23, 15

    View full-size slide

  3. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Change in roles/skills
    3
    when I started
    25 years ago
    Sunday, August 23, 15

    View full-size slide

  4. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Change in roles/skills
    Historically
    System Architect(s)
    Senior Analyst(s)
    Developer(s)
    QA Tester(s)
    Database Administrator(s)
    System Administrator(s)
    3
    when I started
    25 years ago
    Sunday, August 23, 15

    View full-size slide

  5. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Change in roles/skills
    Historically
    System Architect(s)
    Senior Analyst(s)
    Developer(s)
    QA Tester(s)
    Database Administrator(s)
    System Administrator(s)
    3
    Now
    Software Developer
    Software Engineer
    Coder
    Entrepreneur
    when I started
    25 years ago
    Sunday, August 23, 15

    View full-size slide

  6. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Change in roles/skills
    Historically
    System Architect(s)
    Senior Analyst(s)
    Developer(s)
    QA Tester(s)
    Database Administrator(s)
    System Administrator(s)
    3
    Now
    Software Developer
    Software Engineer
    Coder
    Entrepreneur
    when I started
    25 years ago
    Past 5+ years
    [startups]
    Sunday, August 23, 15

    View full-size slide

  7. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    1. SQL Basics
    2. SQL Constructs to avoid
    3. SQL Constructs to use
    4. Use Transactions
    5. Do Less Work
    ...
    4
    EFFECTIVE TIPS
    Sunday, August 23, 15

    View full-size slide

  8. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    6. The problem with timezones
    7. The lack of standards
    8. Read the MySQL documentation
    5
    effective TIPS
    Sunday, August 23, 15

    View full-size slide

  9. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    1
    SQL Basics
    6
    Sunday, August 23, 15

    View full-size slide

  10. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Comment your SQL
    Add a C style comment to any/all SQL
    Enables DBA to more easily find SQL
    via tools like grep.
    7
    SELECT /* MySQLEMEA2014 Demo */ NOW();
    +---------------------+
    | NOW() |
    +---------------------+
    | 2014-08-09 14:01:18 |
    +---------------------+
    INSERT /* AddGameStats:42 */ INTO stats(counter) VALUES(1);
    mysql -c
    Sunday, August 23, 15

    View full-size slide

  11. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Enable General Log
    For all developer/test environments
    NOT FOR PRODUCTION
    8
    [mysqld]
    general-log=1
    general-log-file=/mysql/log/general.log
    Sunday, August 23, 15

    View full-size slide

  12. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Enable General Log
    For all developer/test environments
    NOT FOR PRODUCTION
    8
    [mysqld]
    general-log=1
    general-log-file=/mysql/log/general.log
    5.1+ syntax
    Sunday, August 23, 15

    View full-size slide

  13. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Enable General Log
    For all developer/test environments
    NOT FOR PRODUCTION
    8
    [mysqld]
    general-log=1
    general-log-file=/mysql/log/general.log
    tail -f /mysql/log/general.log
    ...
    15:01:18 6 Query SELECT /* MySQLEMEA2014 Demo */ NOW()
    15:02:09 6 Query INSERT /* AddGameStats:42 */ INTO stats(counter)
    Demonstrate
    use later
    5.1+ syntax
    Sunday, August 23, 15

    View full-size slide

  14. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Why view queries?
    Developer needs to find bad habits early
    Row At a Time (RAT) processing
    The total queries to be executed
    Turn all caching off (memcache, QC etc)
    Learn about the Query Execution Plan
    (QEP) and how to appreciate EXPLAIN
    Understand the true impact of frameworks
    9
    Sunday, August 23, 15

    View full-size slide

  15. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Why view queries?
    Developer needs to find bad habits early
    Row At a Time (RAT) processing
    The total queries to be executed
    Turn all caching off (memcache, QC etc)
    Learn about the Query Execution Plan
    (QEP) and how to appreciate EXPLAIN
    Understand the true impact of frameworks
    9
    Discuss in
    Point 5
    Sunday, August 23, 15

    View full-size slide

  16. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    frameworks
    CLAIM
    improve speed of development
    Abstract need to know SQL
    REALITY
    Undocumented cost to sub-optimal
    performance, especially with data
    persistence
    10
    Sunday, August 23, 15

    View full-size slide

  17. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    future proof SQL
    Always alias columns when using joins
    SELECT only required columns
    Define columns for INSERTs
    11
    Sunday, August 23, 15

    View full-size slide

  18. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    alias columns
    12
    CREATE TABLE products (
    id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL);
    CREATE TABLE orders (
    id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    qty TINYINT UNSIGNED NOT NULL);
    SELECT product_id, description
    FROM orders o, products p
    WHERE o.product_id = p.id;
    Sunday, August 23, 15

    View full-size slide

  19. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    alias columns
    13
    ALTER TABLE orders ADD description TEXT NULL;
    SELECT product_id, description
    FROM orders o, products p
    WHERE o.product_id = p.id;
    ERROR 1052 (23000): Column 'description' in field list is ambiguous
    SELECT o.product_id, p.description
    FROM orders o, products p
    WHERE o.product_id = p.id;
    Sunday, August 23, 15

    View full-size slide

  20. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    alias columns
    13
    ALTER TABLE orders ADD description TEXT NULL;
    SELECT product_id, description
    FROM orders o, products p
    WHERE o.product_id = p.id;
    ERROR 1052 (23000): Column 'description' in field list is ambiguous
    SELECT o.product_id, p.description
    FROM orders o, products p
    WHERE o.product_id = p.id;
    Sunday, August 23, 15

    View full-size slide

  21. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    FUTURE PROOF INSERT
    14
    INSERT INTO countries
    VALUES ('AU','Australia','Canberra');
    CREATE TABLE countries (
    code CHAR(2) NOT NULL PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    capital VARCHAR(60) NOT NULL
    );
    Sunday, August 23, 15

    View full-size slide

  22. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    FUTURE PROOF INSERT
    14
    INSERT INTO countries
    VALUES ('AU','Australia','Canberra');
    CREATE TABLE countries (
    code CHAR(2) NOT NULL PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    capital VARCHAR(60) NOT NULL
    );

    Sunday, August 23, 15

    View full-size slide

  23. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    FUTURE PROOF INSERT
    14
    INSERT INTO countries
    VALUES ('AU','Australia','Canberra');
    CREATE TABLE countries (
    code CHAR(2) NOT NULL PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    capital VARCHAR(60) NOT NULL
    );
    INSERT INTO countries (code, name,capital)
    VALUES ('US','USA','Washington DC');


    Sunday, August 23, 15

    View full-size slide

  24. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    FUTURE PROOF INSERT
    15
    INSERT INTO countries
    VALUES ('AU','Australia','Canberra');
    ERROR 1136 (21S01): Column count doesn't match value count at row 1
    CREATE TABLE countries (
    code CHAR(2) NOT NULL PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    capital VARCHAR(60) NOT NULL
    );
    ALTER TABLE countries
    ADD population INT UNSIGNED NULL;
    INSERT INTO countries (code, name,capital)
    VALUES ('CA','Canada','Ottawa');
    Sunday, August 23, 15

    View full-size slide

  25. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    FUTURE PROOF INSERT
    15
    INSERT INTO countries
    VALUES ('AU','Australia','Canberra');
    ERROR 1136 (21S01): Column count doesn't match value count at row 1
    CREATE TABLE countries (
    code CHAR(2) NOT NULL PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    capital VARCHAR(60) NOT NULL
    );
    ALTER TABLE countries
    ADD population INT UNSIGNED NULL;

    INSERT INTO countries (code, name,capital)
    VALUES ('CA','Canada','Ottawa');

    Sunday, August 23, 15

    View full-size slide

  26. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    SQL LINT
    valid SQL can produce invalid results
    1 row
    n rows
    16
    SELECT YEAR(created) AS yy, MONTH(created) AS mm,
    COUNT(*) AS cnt, FORMAT(SUM(total),2) AS total
    FROM sales
    GROUP BY YEAR(created), MONTH(created);
    SELECT YEAR(created) AS yy, MONTH(created) AS mm,
    COUNT(*) AS cnt, FORMAT(SUM(total),2) AS total
    FROM sales;
    sql_mode
    helps to identify and
    correct
    Sunday, August 23, 15

    View full-size slide

  27. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    SQL LINT
    valid SQL can produce invalid results
    6,000 rows
    17
    SELECT o.id,o.state,o.zip
    FROM sales_tax o
    LEFT JOIN new_sales_tax n ON (o.state = n.state AND o.zip = n.zip)
    AND n.id IS NULL;
    Sunday, August 23, 15

    View full-size slide

  28. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    SQL LINT
    valid SQL can produce invalid results
    6,000 rows
    17
    SELECT o.id,o.state,o.zip
    FROM sales_tax o
    LEFT JOIN new_sales_tax n ON (o.state = n.state AND o.zip = n.zip)
    WHERE n.id IS NULL;
    SELECT o.id,o.state,o.zip
    FROM sales_tax o
    LEFT JOIN new_sales_tax n ON (o.state = n.state AND o.zip = n.zip)
    AND n.id IS NULL;
    AND is part of ON,
    not part of WHERE
    6 rows
    Sunday, August 23, 15

    View full-size slide

  29. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    RECAP
    SQL Basics
    Comment your SQL
    Log, View and Review ALL SQL
    Future proof your SQL
    A valid SQL syntax is not always correct
    18
    Sunday, August 23, 15

    View full-size slide

  30. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    Non standard SQL
    19
    2
    Sunday, August 23, 15

    View full-size slide

  31. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    MYSQL SYNTAX
    REPLACE
    DELAYED
    IGNORE
    LOW_PRIORITY
    HIGH_PRIORITY
    SHOW & DESC
    20
    Sunday, August 23, 15

    View full-size slide

  32. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    REPLACE
    21
    Sunday, August 23, 15

    View full-size slide

  33. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    REPLACE
    If row exists UPDATE, if does not exist
    then INSERT e.g. UPSERT
    21
    Sunday, August 23, 15

    View full-size slide

  34. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    REPLACE
    If row exists UPDATE, if does not exist
    then INSERT e.g. UPSERT
    NOT THIS, BUT:
    21
    Sunday, August 23, 15

    View full-size slide

  35. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    REPLACE
    If row exists UPDATE, if does not exist
    then INSERT e.g. UPSERT
    NOT THIS, BUT:
    Implemented as DELETE row, then
    INSERT
    21
    Sunday, August 23, 15

    View full-size slide

  36. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    REPLACE
    If row exists UPDATE, if does not exist
    then INSERT e.g. UPSERT
    NOT THIS, BUT:
    Implemented as DELETE row, then
    INSERT
    21
    Also no REPLACE
    TRIGGER option
    Sunday, August 23, 15

    View full-size slide

  37. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    DELAYED/IGNORE
    INSERT DELAYED
    INSERT IGNORE
    Do not provide errors when errors
    occur
    LOW_PRIORITY | HIGH_PRIORITY
    Changing locking strategy
    22
    Does not work
    with InnoDB
    Sunday, August 23, 15

    View full-size slide

  38. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    SHOW
    SHOW
    41 different options in 5.5
    Use INFORMATION_SCHEMA when
    possible
    5.0 - 17 views
    5.1 - 27+ views
    5.5 - 39+ views
    23
    5.6 - 58+ views
    5.7 - 59+ views
    Sunday, August 23, 15

    View full-size slide

  39. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL SQL Constructs
    24
    3
    Sunday, August 23, 15

    View full-size slide

  40. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    INFORMATION SCHEMA
    “INFORMATION_SCHEMA is a database within each
    MySQL instance, the place that stores information
    about all the other databases that the MySQL server
    maintains. The INFORMATION_SCHEMA database
    contains several read-only tables. They are actually
    views, not base tables, so there are no files associated
    with them, and you cannot set triggers on them. Also,
    there is no database directory with that name.”
    25
    http://dev.mysql.com/doc/refman/5.5/en/information-schema.html
    Sunday, August 23, 15

    View full-size slide

  41. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    MYSQL SYNTAX
    Multi-row INSERT
    VALUES (), (), ()....
    26
    TRUNCATE TABLE stats;
    START TRANSACTION;
    INSERT INTO stats(counter)
    VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);
    COMMIT;
    SELECT * FROM stats;
    Sunday, August 23, 15

    View full-size slide

  42. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    MYSQL SYNTAX
    LIMIT [n],[m]
    27
    SELECT counter FROM stats WHERE counter > 2 LIMIT 2;
    +---------+
    | counter |
    +---------+
    | 3 |
    | 4 |
    +---------+
    2 rows in set (0.00 sec)
    SELECT counter FROM stats WHERE counter > 2 LIMIT 3,2;
    +---------+
    | counter |
    +---------+
    | 6 |
    | 7 |
    +---------+
    2 rows in set (0.00 sec)
    Sunday, August 23, 15

    View full-size slide

  43. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    STRING matching
    String comparison is case insensitive by
    default
    28
    SELECT Name,CountryCode FROM City
    WHERE LOWER(name) LIKE 'bris%';
    +----------+-------------+
    | Name | CountryCode |
    +----------+-------------+
    | Brisbane | AUS |
    | Bristol | GBR |
    +----------+-------------+
    http://dev.mysql.com/doc/index-other.html
    SELECT Name,CountryCode FROM City
    WHERE name LIKE 'bris%';
    +----------+-------------+
    | Name | CountryCode |
    +----------+-------------+
    | Brisbane | AUS |
    | Bristol | GBR |
    +----------+-------------+
    Sunday, August 23, 15

    View full-size slide

  44. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    STRING matching
    String comparison is case insensitive by
    default
    28
    SELECT Name,CountryCode FROM City
    WHERE LOWER(name) LIKE 'bris%';
    +----------+-------------+
    | Name | CountryCode |
    +----------+-------------+
    | Brisbane | AUS |
    | Bristol | GBR |
    +----------+-------------+
    http://dev.mysql.com/doc/index-other.html
    SELECT Name,CountryCode FROM City
    WHERE name LIKE 'bris%';
    +----------+-------------+
    | Name | CountryCode |
    +----------+-------------+
    | Brisbane | AUS |
    | Bristol | GBR |
    +----------+-------------+
    No CASE function
    needed
    Sunday, August 23, 15

    View full-size slide

  45. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    STRING matching
    String comparison is case insensitive by
    default
    Character set collation is configurable at
    column, table, schema level
    MySQL supports 70+ collations for 30+
    character sets.
    MySQL does not support function based
    indexes
    29
    Sunday, August 23, 15

    View full-size slide

  46. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    Transactions
    30
    4
    Sunday, August 23, 15

    View full-size slide

  47. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Transactions
    The foundation of RDBMS
    The basis of ACID
    Required for business systems
    BUT
    So often no used in MySQL systems
    WHY?
    31
    Sunday, August 23, 15

    View full-size slide

  48. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    AUTOCOMMIT
    32
    Sunday, August 23, 15

    View full-size slide

  49. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    AUTOCOMMIT
    MySQL defaults with autocommit on
    32
    Sunday, August 23, 15

    View full-size slide

  50. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    AUTOCOMMIT
    MySQL defaults with autocommit on
    Historical default storage engine (MyISAM) did
    not support transactions.
    32
    Sunday, August 23, 15

    View full-size slide

  51. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    AUTOCOMMIT
    MySQL defaults with autocommit on
    Historical default storage engine (MyISAM) did
    not support transactions.
    InnoDB existed in 3.23 (Since 2001)
    32
    Sunday, August 23, 15

    View full-size slide

  52. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    AUTOCOMMIT
    MySQL defaults with autocommit on
    Historical default storage engine (MyISAM) did
    not support transactions.
    InnoDB existed in 3.23 (Since 2001)
    Many open source projects fail to use transactions
    (a necessary good practice)
    32
    Sunday, August 23, 15

    View full-size slide

  53. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    AUTOCOMMIT
    MySQL defaults with autocommit on
    Historical default storage engine (MyISAM) did
    not support transactions.
    InnoDB existed in 3.23 (Since 2001)
    Many open source projects fail to use transactions
    (a necessary good practice)
    New developers have never learned what a
    transaction is. and why they are critical to RDBMS
    32
    SESSION only
    scope until 5.5
    Sunday, August 23, 15

    View full-size slide

  54. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TRANSACTIONS
    The general log tells all
    33
    Sunday, August 23, 15

    View full-size slide

  55. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TRANSACTIONS
    The general log tells all
    33
    Time Id Command Argument
    140803 14:50:04 1 Connect msandbox@localhost on
    1 Init DB test
    140803 14:50:52 1 Query INSERT INTO countries
    VALUES ('AU','Australia','Canberra')
    140803 14:51:11 1 Query INSERT INTO countries (code, name,capital)
    VALUES ('US','USA','Washington DC')
    140803 14:51:13 1 Quit
    What not to see
    Sunday, August 23, 15

    View full-size slide

  56. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TRANSACTIONS
    The general log tells all
    33
    Time Id Command Argument
    140803 14:50:04 1 Connect msandbox@localhost on
    1 Init DB test
    140803 14:50:52 1 Query INSERT INTO countries
    VALUES ('AU','Australia','Canberra')
    140803 14:51:11 1 Query INSERT INTO countries (code, name,capital)
    VALUES ('US','USA','Washington DC')
    140803 14:51:13 1 Quit
    Time Id Command Argument
    140803 14:53:03 2 Connect msandbox@localhost on test
    140803 14:53:22 2 Query START TRANSACTION
    140803 14:53:29 2 Query INSERT INTO countries
    VALUES ('AU','Australia','Canberra')
    140803 14:53:47 2 Query INSERT INTO countries (code, name,capital)
    VALUES ('US','USA','Washington DC')
    140803 14:53:50 2 Query COMMIT
    140803 14:53:54 2 Quit
    What not to see
    Correct Use
    Sunday, August 23, 15

    View full-size slide

  57. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TRANSACTIONS
    34
    Sunday, August 23, 15

    View full-size slide

  58. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TRANSACTIONS
    34
    SELECT variable_name,variable_value
    FROM information_schema.global_status
    WHERE variable_name IN ('com_insert','com_update',
    'com_delete', 'com_commit','com_rollback');
    +---------------+----------------+
    | variable_name | variable_value |
    +---------------+----------------+
    | COM_COMMIT | 2148778 |
    | COM_DELETE | 3614178 |
    | COM_INSERT | 68096778 |
    | COM_ROLLBACK | 85193 |
    | COM_UPDATE | 142914426 |
    +---------------+----------------+
    Sunday, August 23, 15

    View full-size slide

  59. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TRANSACTIONS
    35
    SELECT table_schema,
    SUM(data_length+index_length)/1024/1024 AS total_mb,
    SUM(data_length)/1024/1024 AS data_mb,
    SUM(index_length)/1024/1024 AS index_mb,
    COUNT(*) AS tables,
    CURDATE() AS today
    FROM information_schema.tables
    WHERE engine='MyISAM'
    AND table_schema NOT IN ('mysql','information_schema')
    GROUP BY table_schema
    ORDER BY 2 DESC;
    +--------------------+---------------+---------------+------------+--------+
    | table_schema | total_mb | data_mb | index_mb | tables |
    +--------------------+---------------+---------------+------------+--------+
    | xxxxxxxx | 1418.39929962 | 1412.66883087 | 5.73046875 | 2 |
    | xxxx | 16.89165497 | 9.18071747 | 7.71093750 | 3 |
    | xx_wordpress | 7.82143688 | 7.45424938 | 0.36718750 | 13 |
    | xxxxxxxxx_drupal | 2.17076111 | 1.86607361 | 0.30468750 | 62 |
    MyISAM Tables
    Sunday, August 23, 15

    View full-size slide

  60. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    use correctly
    36
    Sunday, August 23, 15

    View full-size slide

  61. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    use correctly
    Abuse of Transactions
    36
    Sunday, August 23, 15

    View full-size slide

  62. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    use correctly
    Abuse of Transactions
    One business function, 3 transactions
    36
    Sunday, August 23, 15

    View full-size slide

  63. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    use correctly
    Abuse of Transactions
    One business function, 3 transactions
    Start transaction, then programming
    logic (taking time to execute)
    36
    Sunday, August 23, 15

    View full-size slide

  64. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    use correctly
    Abuse of Transactions
    One business function, 3 transactions
    Start transaction, then programming
    logic (taking time to execute)
    Additional SELECT’s inside transaction
    36
    Sunday, August 23, 15

    View full-size slide

  65. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    use correctly
    Abuse of Transactions
    One business function, 3 transactions
    Start transaction, then programming
    logic (taking time to execute)
    Additional SELECT’s inside transaction
    Frameworks exhibit these patterns
    36
    Sunday, August 23, 15

    View full-size slide

  66. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective Software Development with MySQL
    25+ years Experience
    37
    “If your application (in-house, open-
    source, purchased) does not use
    transactions at all times, there will
    be far greater problems and long
    term increased cost of ownership.”
    Ronald Bradford
    August 2014
    Sunday, August 23, 15

    View full-size slide

  67. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    Do Less Work
    38
    5
    Sunday, August 23, 15

    View full-size slide

  68. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective Software Development with MySQL
    25+ years Experience
    39
    “Every MySQL application executes
    more SQL than necessary. Reducing,
    removing and combining SQL
    queries will improve performance
    and increase throughput capacity.”
    Ronald Bradford
    August 2014
    Sunday, August 23, 15

    View full-size slide

  69. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    examples
    40
    Sunday, August 23, 15

    View full-size slide

  70. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    examples
    Duplicate identical queries
    40
    Sunday, August 23, 15

    View full-size slide

  71. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    examples
    Duplicate identical queries
    Repeating queries
    40
    Sunday, August 23, 15

    View full-size slide

  72. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    examples
    Duplicate identical queries
    Repeating queries
    Queries the can be combined
    40
    Sunday, August 23, 15

    View full-size slide

  73. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    examples
    Duplicate identical queries
    Repeating queries
    Queries the can be combined
    Queries that can be simplified
    40
    Sunday, August 23, 15

    View full-size slide

  74. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    examples
    Duplicate identical queries
    Repeating queries
    Queries the can be combined
    Queries that can be simplified
    Queries that can be removed
    40
    Sunday, August 23, 15

    View full-size slide

  75. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    RAT processing
    41
    SELECT name FROM firms WHERE id=727;
    SELECT name FROM firms WHERE id=758;
    SELECT name FROM firms WHERE id=857;
    SELECT name FROM firms WHERE id=740;
    SELECT name FROM firms WHERE id=849;
    SELECT name FROM firms WHERE id=839;
    SELECT name FROM firms WHERE id=847;
    SELECT name FROM firms WHERE id=867;
    SELECT name FROM firms WHERE id=829;
    SELECT name FROM firms WHERE id=812;
    SELECT name FROM firms WHERE id=868;
    SELECT name FROM firms WHERE id=723;
    SELECT id, name
    FROM firms
    WHERE id IN (723, 727, 740, 758, 812, 829, 839, 847, 849, 857, 867, 868);
    Sunday, August 23, 15

    View full-size slide

  76. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    RAT processing
    41
    SELECT name FROM firms WHERE id=727;
    SELECT name FROM firms WHERE id=758;
    SELECT name FROM firms WHERE id=857;
    SELECT name FROM firms WHERE id=740;
    SELECT name FROM firms WHERE id=849;
    SELECT name FROM firms WHERE id=839;
    SELECT name FROM firms WHERE id=847;
    SELECT name FROM firms WHERE id=867;
    SELECT name FROM firms WHERE id=829;
    SELECT name FROM firms WHERE id=812;
    SELECT name FROM firms WHERE id=868;
    SELECT name FROM firms WHERE id=723;
    Classic N+1 problem
    SELECT id, name
    FROM firms
    WHERE id IN (723, 727, 740, 758, 812, 829, 839, 847, 849, 857, 867, 868);


    Sunday, August 23, 15

    View full-size slide

  77. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    CAT processing
    42
    SET PROFILING=1;
    SELECT ...
    SHOW PROFILES;
    +----------+------------+---------------------------------------------------------
    | Query_ID | Duration | Query
    +----------+------------+---------------------------------------------------------
    | 1 | 0.00030400 | SELECT name FROM firms WHERE id=727
    | 2 | 0.00014400 | SELECT name FROM firms WHERE id=758
    | 3 | 0.00014300 | SELECT name FROM firms WHERE id=857
    | 4 | 0.00014000 | SELECT name FROM firms WHERE id=740
    | 5 | 0.00012300 | SELECT name FROM firms WHERE id=849
    | 6 | 0.00012200 | SELECT name FROM firms WHERE id=839
    | 7 | 0.00011600 | SELECT name FROM firms WHERE id=847
    | 8 | 0.00014300 | SELECT name FROM firms WHERE id=867
    | 9 | 0.00013900 | SELECT name FROM firms WHERE id=829
    | 10 | 0.00014000 | SELECT name FROM firms WHERE id=812
    | 11 | 0.00012800 | SELECT name FROM firms WHERE id=868
    | 12 | 0.00011700 | SELECT name FROM firms WHERE id=723
    | 13 | 0.00031100 | SELECT id, name FROM firms WHERE id IN (723 ...
    +----------+------------+---------------------------------------------------------
    Sunday, August 23, 15

    View full-size slide

  78. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    CAT processing
    42
    SET PROFILING=1;
    SELECT ...
    SHOW PROFILES;
    +----------+------------+---------------------------------------------------------
    | Query_ID | Duration | Query
    +----------+------------+---------------------------------------------------------
    | 1 | 0.00030400 | SELECT name FROM firms WHERE id=727
    | 2 | 0.00014400 | SELECT name FROM firms WHERE id=758
    | 3 | 0.00014300 | SELECT name FROM firms WHERE id=857
    | 4 | 0.00014000 | SELECT name FROM firms WHERE id=740
    | 5 | 0.00012300 | SELECT name FROM firms WHERE id=849
    | 6 | 0.00012200 | SELECT name FROM firms WHERE id=839
    | 7 | 0.00011600 | SELECT name FROM firms WHERE id=847
    | 8 | 0.00014300 | SELECT name FROM firms WHERE id=867
    | 9 | 0.00013900 | SELECT name FROM firms WHERE id=829
    | 10 | 0.00014000 | SELECT name FROM firms WHERE id=812
    | 11 | 0.00012800 | SELECT name FROM firms WHERE id=868
    | 12 | 0.00011700 | SELECT name FROM firms WHERE id=723
    | 13 | 0.00031100 | SELECT id, name FROM firms WHERE id IN (723 ...
    +----------+------------+---------------------------------------------------------
    SELECT 'Sum Individual Queries' AS txt,SUM(DURATI
    INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID BETWE
    UNION
    SELECT 'Combined Query',SUM(DURATION) FROM INFORM
    QUERY_ID = 13;
    +------------------------+------------+
    | txt | total_time |
    +------------------------+------------+
    | Sum Individual Queries | 0.001311 |
    | Combined Query | 0.000311 |
    +------------------------+------------+
    4X longer processing for
    every page load
    Instant DB Scalability
    Sunday, August 23, 15

    View full-size slide

  79. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    simplicity
    43
    Sunday, August 23, 15

    View full-size slide

  80. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    simplicity
    Before considering how do I fix this
    code/SQL, ask?
    43
    Sunday, August 23, 15

    View full-size slide

  81. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    simplicity
    Before considering how do I fix this
    code/SQL, ask?
    Can I remove or simplify this code/SQL?
    Does this process use this data?
    43
    Sunday, August 23, 15

    View full-size slide

  82. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    framework cost
    Drupal “Framework” + customization
    50 SQL statements to create a user
    Only13 actual SQL statements required with
    sub-optimal design
    4-5 SQL statements with optimal design
    44
    Sunday, August 23, 15

    View full-size slide

  83. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    framework cost
    Drupal “Framework” + customization
    50 SQL statements to create a user
    Only13 actual SQL statements required with
    sub-optimal design
    4-5 SQL statements with optimal design
    44
    If this exists in the most
    fundamental core, it exists
    everywhere in code
    Sunday, August 23, 15

    View full-size slide

  84. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    45
    Deleting Code
    Sunday, August 23, 15

    View full-size slide

  85. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL and Timezones
    46
    6
    Sunday, August 23, 15

    View full-size slide

  86. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONE FUNCTIONS
    47
    Sunday, August 23, 15

    View full-size slide

  87. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONE FUNCTIONS
    Will your users ever cross timezones?
    47
    Sunday, August 23, 15

    View full-size slide

  88. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONE FUNCTIONS
    Will your users ever cross timezones?
    Will your application data ever support
    multiple timezones?
    47
    Sunday, August 23, 15

    View full-size slide

  89. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONE FUNCTIONS
    Will your users ever cross timezones?
    Will your application data ever support
    multiple timezones?
    CONVERT_TZ(date,from_tz,to_tz)
    47
    Sunday, August 23, 15

    View full-size slide

  90. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONE FUNCTIONS
    Will your users ever cross timezones?
    Will your application data ever support
    multiple timezones?
    CONVERT_TZ(date,from_tz,to_tz)
    Does not work by default
    47
    Sunday, August 23, 15

    View full-size slide

  91. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Timezones
    Timezone data is not loaded by default
    48
    mysql> SELECT COUNT(*) AS cnt FROM mysql.time_zone;
    +-----+
    | cnt |
    +-----+
    | 0 |
    +-----+
    mysql> SELECT NOW(),
    -> CONVERT_TZ(NOW(),'America/New_York','UTC') AS utc;
    +---------------------+------+
    | NOW() | utc |
    +---------------------+------+
    | 2014-08-02 17:05:18 | NULL |
    +---------------------+------+
    http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
    Sunday, August 23, 15

    View full-size slide

  92. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Timezones
    49
    $ mysql_tzinfo_to_sql /usr/share/zoneinfo | \
    mysql -u root mysql
    mysql> SELECT COUNT(*) AS cnt FROM mysql.time_zone;
    +------+
    | cnt |
    +------+
    | 1738 |
    +------+
    mysql> SELECT NOW(),
    -> CONVERT_TZ(NOW(),'America/New_York','UTC') AS utc;
    +---------------------+---------------------+
    | NOW() | utc |
    +---------------------+---------------------+
    | 2014-08-02 16:14:04 | 2014-08-02 20:14:04 |
    +---------------------+---------------------+
    http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html
    Sunday, August 23, 15

    View full-size slide

  93. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONES
    is enabled by default
    50
    SELECT @@time_zone;
    +-------------+
    | @@time_zone |
    +-------------+
    | SYSTEM |
    +-------------+
    [mysqld]
    default_time_zone='America/Chicago';
    SELECT @@time_zone;
    +-----------------+
    | @@time_zone |
    +-----------------+
    | America/Chicago |
    +-----------------+
    Sunday, August 23, 15

    View full-size slide

  94. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    TIMEZONES
    is enabled by default
    50
    SELECT @@time_zone;
    +-------------+
    | @@time_zone |
    +-------------+
    | SYSTEM |
    +-------------+
    [mysqld]
    default_time_zone='America/Chicago';
    SELECT @@time_zone;
    +-----------------+
    | @@time_zone |
    +-----------------+
    | America/Chicago |
    +-----------------+

    Does not tell you
    the actual TZ
    Sunday, August 23, 15

    View full-size slide

  95. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    MYSQL SYNTAX
    Do not use system global in coding
    String value not always loaded
    Set at startup, does not change for
    daylight savings
    51
    SELECT @@system_time_zone;
    +--------------------+
    | @@system_time_zone |
    +--------------------+
    | EDT |
    +--------------------+
    SELECT CONVERT_TZ(NOW(),@@system_time_zone,'UTC') AS wrong,
    CONVERT_TZ(NOW(),@@time_zone,'UTC') AS correct;
    +-------+---------------------+
    | wrong | correct |
    +-------+---------------------+
    | NULL | 2014-08-05 02:07:18 |
    +-------+---------------------+
    Sunday, August 23, 15

    View full-size slide

  96. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    MYSQL SYNTAX
    Do not use system global in coding
    String value not always loaded
    Set at startup, does not change for
    daylight savings
    51
    SELECT @@system_time_zone;
    +--------------------+
    | @@system_time_zone |
    +--------------------+
    | EDT |
    +--------------------+
    SELECT CONVERT_TZ(NOW(),@@system_time_zone,'UTC') AS wrong,
    CONVERT_TZ(NOW(),@@time_zone,'UTC') AS correct;
    +-------+---------------------+
    | wrong | correct |
    +-------+---------------------+
    | NULL | 2014-08-05 02:07:18 |
    +-------+---------------------+

    Sunday, August 23, 15

    View full-size slide

  97. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    Having Standards
    52
    7
    Sunday, August 23, 15

    View full-size slide

  98. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    about standards
    53
    Sunday, August 23, 15

    View full-size slide

  99. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    about standards
    This presentation is not about defining
    THE standard to use
    53
    Sunday, August 23, 15

    View full-size slide

  100. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    about standards
    This presentation is not about defining
    THE standard to use
    This is about stating you HAVE a
    standard that meets your needs, you
    USE it, and ENFORCE it
    53
    Sunday, August 23, 15

    View full-size slide

  101. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    about standards
    This presentation is not about defining
    THE standard to use
    This is about stating you HAVE a
    standard that meets your needs, you
    USE it, and ENFORCE it
    Some recommendations...
    53
    Sunday, August 23, 15

    View full-size slide

  102. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    BE CONSISTENT
    Name columns consistently
    column for date/time a row is created
    54
    created_date DATETIME
    datetime DATETIME
    created_at DATETIME
    created TIMESTAMP
    Sunday, August 23, 15

    View full-size slide

  103. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    BE CONSISTENT
    Name columns consistently
    column for date/time a row is created
    54
    created_date DATETIME
    datetime DATETIME
    created_at DATETIME
    created TIMESTAMP
    One data model, at
    least 4 variances
    Sunday, August 23, 15

    View full-size slide

  104. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    primary key
    Name primary key unique across system
    Where is the data model
    55
    CREATE TABLE order (
    order_id INT UNSIGNED NOT NULL PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    CREATE TABLE customer (
    customer_id INT UNSIGNED NOT NULL PRIMARY KEY,
    CREATE TABLE order (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    CREATE TABLE customer (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    Sunday, August 23, 15

    View full-size slide

  105. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    primary key
    Name primary key unique across system
    Where is the data model
    55
    CREATE TABLE order (
    order_id INT UNSIGNED NOT NULL PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    CREATE TABLE customer (
    customer_id INT UNSIGNED NOT NULL PRIMARY KEY,
    CREATE TABLE order (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    CREATE TABLE customer (
    id INT UNSIGNED NOT NULL PRIMARY KEY,

    Sunday, August 23, 15

    View full-size slide

  106. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    documentation
    Where is the data model?
    56
    Sunday, August 23, 15

    View full-size slide

  107. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    reserved words
    Do not use reserved words as columns
    datetime
    date
    from
    to
    57
    Sunday, August 23, 15

    View full-size slide

  108. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    reserved words
    Do not use reserved words as columns
    datetime
    date
    from
    to
    57
    MySQL allows use of
    reserved words using
    `backtick` syntax
    Sunday, August 23, 15

    View full-size slide

  109. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    table names
    Name tables either singular or plural
    Again, be consistent
    Professionally, singular wins because it
    is simpler
    order, order_line,country
    orders, order[s]_lines, countries
    58
    Sunday, August 23, 15

    View full-size slide

  110. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    Learn the Documentation
    59
    8
    Sunday, August 23, 15

    View full-size slide

  111. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    DOCS
    60
    http://dev.mysql.com/doc/
    Sunday, August 23, 15

    View full-size slide

  112. Effective Software Development with MySQL
    #mysql @RonaldBradford EffectiveMySQL.com
    What next?
    61
    ?
    Sunday, August 23, 15

    View full-size slide

  113. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  114. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    Developer should learn EXPLAIN
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  115. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    Developer should learn EXPLAIN
    Scrub/Subset your data
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  116. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    Developer should learn EXPLAIN
    Scrub/Subset your data
    Developer should not use production
    data
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  117. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    Developer should learn EXPLAIN
    Scrub/Subset your data
    Developer should not use production
    data
    Actively remove unused tables/columns
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  118. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    Developer should learn EXPLAIN
    Scrub/Subset your data
    Developer should not use production
    data
    Actively remove unused tables/columns
    Do manual code deployments
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  119. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    More
    Developer should learn EXPLAIN
    Scrub/Subset your data
    Developer should not use production
    data
    Actively remove unused tables/columns
    Do manual code deployments
    Enforce a QA step
    62
    Today’s Developers
    Sunday, August 23, 15

    View full-size slide

  120. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    concluding top Tips
    Do Less Work
    Ensure developers learn how to read
    and write SQL
    Use Transactions
    Read the MySQL documentation
    63
    Sunday, August 23, 15

    View full-size slide

  121. EffectiveMySQL.com
    #mysql #emea2014
    Effective Software Development with MySQL
    Trivia Tip
    --i-am-a-dummy
    Restricts UPDATE/DELETE statements
    Alias for --safe-updates
    http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_safe-updates
    64
    Sunday, August 23, 15

    View full-size slide

  122. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective Software Development with MySQL
    Conclusion
    Copies of these slides can be found at
    http://effectiveMySQL.com
    [email protected]
    65
    Sunday, August 23, 15

    View full-size slide