Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

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