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.

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