with MySQL / 26 years with RDBMS 10th year presenting MySQL / Open Source / Cloud / PHP Run NY MySQL Meetup Published author of 4 books MySQL Community Member of the year (09,13) Oracle ACE Director Ronald BRADFORD www.meetup.com/EffectiveMySQL/
MAX(LENGTH(column)) AS len FROM table; +----------+ | len | +----------+ | 162287 | +----------+ mysql> mysql> SELECT LENGTH(column) AS len, LENGTH(COMPRESS(column)) AS zip, FORMAT(100-(LENGTH(COMPRESS(column))/ LENGTH(column)*100),2) AS pct FROM column WHERE id=?; +--------+-------+-------+ | len | zip | pct | +--------+-------+-------+ | 162287 | 25670 | 84.18 | +--------+-------+-------+ DB 84% compression
is a HTTP request HTTP 1.1 Persistent Connections Limit based on browser https://developer.chrome.com/devtools/docs/network#resource-network-timing Web
option_name, option_value FROM wp_options WHERE autoload = 'yes' SELECT option_value FROM wp_options WHERE option_name = 'aiosp_title_format' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_show_only_even' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_num_months' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_day_length' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_hide_event_box' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_advanced' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_navigation' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_disable_popups' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'sidebars_widgets' LIMIT 1 Wordpress Example http://ronaldbradford.com/blog/dp8-the-disadvantages-of-row-at-a-time-processing-2013-08-05/ http://ronaldbradford.com/blog/the-rat-and-the-cat-2006-08-24/ http://ronaldbradford.com/blog/optimizing-sql-performance-the-art-of-elimination-2010-07-08/ http://ronaldbradford.com/blog/simple-lessons-in-improving-scalability-2011-02-16/ DB
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); ✘ DB ✔ Classic N+1 problem
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 DB
Log (All versions) NOT FOR PRODUCTION USE Performance Schema (5.6+) events_statements_... DB [mysqld] general-log=1 general-log-file=/mysql/log/general.log https://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-tables.html
tells all DB 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 What not to see
with MySQL Common MySQL Scalability Mistakes Other Presentations http://effectivemysql.com/presentation/common-mysql-scalability-mistakes/ http://ronaldbradford.com/presentations/ http://effectivemysql.com/event/developing-modern-applications-using-mysql-seminar/
45ms 175ms to 10ms to 3ms 24 queries to 3 queries http://ronaldbradford.com/blog/improving-performance-a-full-stack-problem-2015-03-06/ http://ronaldbradford.com/blog/improving-performance-with-better-indexes-2015-04-14/ Available now for consulting #Hire Me
ronaldbradford
caori_t
mmclsntr
keigotomomatsu
kawaguti
ak1210
takatama
vtryo
andyatkinson
line_developers_tw
line_developers
carta_engineering
egmc
colly
robhawkes
scottboms
frogandcode
rmw
lara
malarkey
mza
addyosmani
shpigford
keavy
searls
