Archive for the ‘MySQL DBA Techniques’ tag
MySQL Query Performance
Working through our chapter on MySQL views, I wrote the query two ways to introduce the idea of SQL tuning. That’s one of the final topics before introducing JSON types.
I gave the students this query based on the Sakila sample database after explaining how to use the EXPLAIN syntax. The query only uses only inner joins, which are generally faster and more efficient than subqueries as a rule of thumb than correlated subqueries.
SELECT ctry.country AS country_name , SUM(p.amount) AS tot_payments FROM city c INNER JOIN address a ON c.city_id = a.city_id INNER JOIN customer cus ON a.address_id = cus.address_id INNER JOIN payment p ON cus.customer_id = p.customer_id INNER JOIN country ctry ON c.country_id = ctry.country_id GROUP BY ctry.country; |
It generated the following tabular explain plan output:
+----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+ | 1 | SIMPLE | cus | NULL | index | PRIMARY,idx_fk_address_id | idx_fk_address_id | 2 | NULL | 599 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | a | NULL | eq_ref | PRIMARY,idx_fk_city_id | PRIMARY | 2 | sakila.cus.address_id | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | eq_ref | PRIMARY,idx_fk_country_id | PRIMARY | 2 | sakila.a.city_id | 1 | 100.00 | NULL | | 1 | SIMPLE | ctry | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.c.country_id | 1 | 100.00 | NULL | | 1 | SIMPLE | p | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.cus.customer_id | 26 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+ 5 rows in set, 1 warning (0.02 sec) |
Then, I used MySQL Workbench to generate the following visual explain plan:
Then, I compared it against a refactored version of the query that uses a correlated subquery in the SELECT-list. The example comes form Appendix B in Learning SQL, 3rd Edition by Alan Beaulieu.
SELECT ctry.country , (SELECT SUM(p.amount) FROM city c INNER JOIN address a ON c.city_id = a.city_id INNER JOIN customer cus ON a.address_id = cus.address_id INNER JOIN payment p ON cus.customer_id = p.customer_id WHERE c.country_id = ctry.country_id) AS tot_payments FROM country ctry; |
It generated the following tabular explain plan output:
+----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+ | 1 | PRIMARY | ctry | NULL | ALL | NULL | NULL | NULL | NULL | 109 | 100.00 | NULL | | 2 | DEPENDENT SUBQUERY | c | NULL | ref | PRIMARY,idx_fk_country_id | idx_fk_country_id | 2 | sakila.ctry.country_id | 5 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | a | NULL | ref | PRIMARY,idx_fk_city_id | idx_fk_city_id | 2 | sakila.c.city_id | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | cus | NULL | ref | PRIMARY,idx_fk_address_id | idx_fk_address_id | 2 | sakila.a.address_id | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | p | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.cus.customer_id | 26 | 100.00 | NULL | +----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+ 5 rows in set, 2 warnings (0.00 sec) |
and, MySQL Workbench generated the following visual explain plan:
The tabular explain plan identifies the better performing query to an experienced eye but the visual explain plan works better for those new to SQL tuning.
The second query performs best because it reads the least data by leveraging the indexes best. As always, I hope these examples help those looking at learning more about MySQL.
Express.js & MySQL
Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:
- Configure your Express.js and MySQL development in a single Node.js application.
- How to convert the list of
RowDataPacketobjects as elements of data, which is really just simple JavaScript knowledge. - How to bind variables into the query.
Like the other blog post, this one assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.
Before you write the Node.js applicaiton, you need to setup a db developer directory. A global install of Node.js means you need to create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory (in Linux). You can use the following Linux command from within the db directory to create the appropriate symbolic link:
ln -s /usr/local/lib/node_modules `pwd`/node_modules |
or, assuming you have a /home/some_user/db directory
ln -s /usr/local/lib/node_modules /home/some_user/node_modules |
After creating the node_modules symbolic link, you need to run the following two npm commands. Please note that second command holds the secret-sauce for generating a package.json file that supports Express.js and the MySQL driver:
npm init --y sudo npm install --save express mysql |
Then, you need to replace the package.json file with the contents of the package-lock.json file from your last npm command.
Here’s a small sample program that uses Express.js, converts the RowDataPackets collection, and binds local variables into the query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | // Require libraries. const express = require('express') const mysql = require('mysql') // Create a mysql connection. const connection = mysql.createConnection({ host: 'localhost', user: 'student', password: 'student', database: 'studentdb' }) // Declare two local variables. const start_date = '2001-01-01' const end_date = '2003-12-31' // Connect and display results in the console log. connection.connect((err) => { if (err) throw err else { console.log('Connected to MySQL Server!\n') connection.query("SELECT i.item_title " + ", date_format(i.release_date,'%d-%M-%Y') AS release_date " + "FROM item i JOIN common_lookup cl " + "ON i.item_type = cl.common_lookup_id " + "WHERE cl.common_lookup_type = 'BLU-RAY' " + "AND i.release_date BETWEEN ? AND ? " + "ORDER BY i.release_date" ,[start_date, end_date], function (err, result) { if (err) throw err else { // Prints the index value in the RowDataPacket. for(let element in result) { console.log(result[element].item_title + ', ' + result[element].release_date) } console.log('') console.log('Press Ctrl-C to terminate ...') } }) } }) |
Line 28 shows two question marks. They act as placeholders for binding variables. Then, on line 30 you see a collection of the start_date and end_date local variables, which is the second argument to the query() function.
Rather than define individual variables, you can pass them as a collection directly. For example, you replace lines 14 and 15 with this single line:
14 | const dates = ['2001-01-01','2003-12-31'] |
Then, you can pass dates as the second argument to the query() function, like this:
30 | ,dates, function (err, result) { |
Lines 35 and 36 show you how to convert a collection of RowDataPacket objects into elements of data. The for loop assigns the index value to the element variable, which lets you address a single RowDataPacket object instance. The dot (“.“) notation lets you use the name in a name-value pair to reference its value.
It displays the following:
Connected to MySQL Server! Star Wars II, 16-May-2002 Harry Potter and the Chamber of Secrets, 28-May-2002 Harry Potter and the Sorcerer's Stone, 28-May-2002 Die Another Day, 03-June-2003 Press Ctrl-C to terminate ... |
Naturally, I’ll get around to writing something up that shows how to leverage MySQL data into a dynamic form with Handlebars at some point in the near future.
As always, I hope this helps those looking for a solution.
Fedora LAMP Steps
I posted earlier in the year how to configure a Fedora instance to test PHP code on a local VM. However, I’ve got a few questions on how to find those posts. Here’s a consolidation with links on those steps:
- Go to this blog post and install the
httpdandphplibraries with theyuminstaller. - In the same blog post as step 1 (you can put the sample PHP code into the
/var/www/htmldirectory for testing), connect to theyumshell and remove thephp-mysqllibrary and then install themysqlndlibrary. - Go to this blog post and install the
php-gdlibraries, which enable you to render PNG images stored as binary streams in MySQL.
As always, I hope that helps.
Querying InnoDB Tables
Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation |
It’s easy to fix the error, except you must grant the PROCESS privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege global PROCESS privilege to the student user with the following command:
GRANT PROCESS ON *.* TO student; |
Then, you can run this query to resolve foreign keys to their referenced primary key column values:
SELECT SUBSTRING_INDEX(f.id,'/',-1) AS constraint_name , CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) AS foreign_key_column , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)) AS primary_key_column FROM innodb_sys_foreign f INNER JOIN innodb_sys_foreign_cols fc ON f.id = fc.id WHERE SUBSTRING_INDEX(f.for_name,'/',-1) = 'system_user_lab' ORDER BY CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)); |
It returns the following:
+---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | primary_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) |
However, you can get the same information without granting the global PROCESS privilege. You simply use the table_constraints and key_column_usage tables, like this:
SELECT tc.constraint_name , CONCAT(kcu.table_name,'.',kcu.column_name) AS foreign_key_column , CONCAT(kcu.referenced_table_name,'.',kcu.referenced_column_name) AS primary_key_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'foreign key' AND tc.table_name = 'system_user_lab' ORDER BY tc.table_name , kcu.column_name; |
It prints the same output:
+---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | primary_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) |
Hope this helps.
Popular PHP Frameworks
My students often ask me about popular PHP frameworks and MySQL approaches. I wish a PHP framework choice was as clear as a JavaScript framework, like AngularJS. Unfortunately, PHP frameworks aren’t that clear cut.
It seems that the most popular PHP frameworks are: Laravel (1st), Phalcon (2nd), and Symphony2 (3rd). I found the following graphic (from December 2013) that highlights popularity by percentage of the market (though I can’t guarantee its accuracy). As far as jobs go, on Dice.com only Laravel had more than 3 positions referring to the framework by name. There were actually 42 PHP developer positions that mention Laravel out of 2,115 PHP developer positions. So, it seems learning a specific framework for PHP doesn’t yet have much market appeal.
While learning the basics of PHP are generic, frameworks expedite process and control pattern implementation. My suggestion to students is to target three to five employers that use a specific framework and start learning how to use the framework. I’d like to get opinions from those in the field whether this is the best advice.
As to MySQL, I suggest they learn native Mysqli and MySQL PDO. Any other suggestions on that from readers? Thanks as always.
Cleanup a MySQL Schema
My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.
Here’s the dropForeignKeys stored procedure, but if you want to capture ALTER statements that add these back later please check my follow-up Capture MySQL Foreign Keys post.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | -- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropForeignKeys; -- Provide a log file debugging statement. SELECT 'CREATE PROCEDURE dropForeignKeys'; -- Change delimiter to create procedure. DELIMITER $$ -- Create procedure. CREATE PROCEDURE dropForeignKeys ( pv_database VARCHAR(64) , pv_referenced_table VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE foreign_key_cursor CURSOR FOR SELECT rc.table_name , rc.constraint_name FROM information_schema.referential_constraints rc WHERE constraint_schema = IFNULL(pv_database,database()) AND referenced_table_name = pv_referenced_table ORDER BY rc.table_name , rc.constraint_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open a local cursor. */ OPEN foreign_key_cursor; cursor_foreign_key: LOOP FETCH foreign_key_cursor INTO lv_table_name , lv_constraint_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF; /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('ALTER TABLE',' ',lv_table_name,' ','DROP FOREIGN KEY',' ',lv_constraint_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_foreign_key; CLOSE foreign_key_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
Here’s the dropViews stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | -- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropViews'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropViews; -- Provide a log file debugging statement. SELECT 'CREATE PROCEDURE dropViews'; -- Change delimiter to create procedure. DELIMITER $$ -- Create procedure. CREATE PROCEDURE dropViews ( pv_database VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_view_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE view_cursor CURSOR FOR SELECT v.table_name FROM information_schema.views v WHERE table_schema = IFNULL(pv_database, database()) ORDER BY v.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open a local cursor. */ OPEN view_cursor; cursor_view: LOOP FETCH view_cursor INTO lv_view_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_view; END IF; /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('DROP VIEW',' ',lv_view_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_view; CLOSE view_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
Here’s the dropTables stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | CREATE PROCEDURE dropTables ( pv_database VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_table_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE table_cursor CURSOR FOR SELECT t.table_name FROM information_schema.tables t WHERE table_schema = IFNULL(pv_database, database()) ORDER BY t.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Drop the views. */ CALL dropViews(null); /* Open a local cursor. */ OPEN table_cursor; cursor_table: LOOP FETCH table_cursor INTO lv_table_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_table; END IF; /* Drop the tables. */ CALL dropForeignKeys(null,lv_table_name); /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('DROP TABLE',' ',lv_table_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_table; CLOSE table_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
You put these in a rerunnable script, run it, and then call the dropTables stored procedure. You can pass a database (or schema) name or a null value. When you pass a null value, it uses the current database, like:
CALL dropTables(null); |
As always, I hope this helps others.
Subscribe via RSS