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.

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

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
  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
  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
  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
  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
  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
  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
  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
  9. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com 1 SQL

    Basics 6 Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  30. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com Non standard

    SQL 19 2 Sunday, August 23, 15
  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
  32. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL REPLACE 21

    Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  39. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com MySQL SQL

    Constructs 24 3 Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  46. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com Transactions 30

    4 Sunday, August 23, 15
  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
  48. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL AUTOCOMMIT 32

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

    defaults with autocommit on 32 Sunday, August 23, 15
  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
  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
  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
  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
  54. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL TRANSACTIONS The

    general log tells all 33 Sunday, August 23, 15
  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
  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
  57. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL TRANSACTIONS 34

    Sunday, August 23, 15
  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
  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
  60. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL use correctly

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

    Abuse of Transactions 36 Sunday, August 23, 15
  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
  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
  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
  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
  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
  67. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com Do Less

    Work 38 5 Sunday, August 23, 15
  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
  69. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL examples 40

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

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

    identical queries Repeating queries 40 Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  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
  79. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL simplicity 43

    Sunday, August 23, 15
  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
  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
  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
  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
  84. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL 45 Deleting

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

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

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

    Will your users ever cross timezones? 47 Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  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
  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
  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
  97. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com Having Standards

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

    53 Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  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
  106. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL documentation Where

    is the data model? 56 Sunday, August 23, 15
  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
  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
  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
  110. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com Learn the

    Documentation 59 8 Sunday, August 23, 15
  111. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL DOCS 60

    http://dev.mysql.com/doc/ Sunday, August 23, 15
  112. Effective Software Development with MySQL #mysql @RonaldBradford EffectiveMySQL.com What next?

    61 ? Sunday, August 23, 15
  113. EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL More 62

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

    should learn EXPLAIN 62 Today’s Developers Sunday, August 23, 15
  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
  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
  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
  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
  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
  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
  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
  122. EffectiveMySQL.com #mysql @RonaldBradford Effective Software Development with MySQL Conclusion Copies

    of these slides can be found at http://effectiveMySQL.com ronald@EffectiveMySQL.com 65 Sunday, August 23, 15