MySQL

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 ... [More] 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. [Less]

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 ... [More] explaining how to use the EXPLAIN syntax. The query only uses only inner joins, which are always faster and more efficient than subqueries or 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. [Less]

MySQL Transaction Reporting In chapter 8 of my forthcoming book (more on this later), I discuss reporting MySQL transactions (among many other related topics). In the book, I note that the state of the art is practically nonexistent: transactions are ... [More] important, but we just don’t monitor or report them in the world of MySQL. Historically (in older versions of MySQL), there was basically no way to do this because neither the general log nor the slow log printed transaction IDs. [Less]

In this tutorial, we are going to show you how to install MySQL on your Ubuntu 20.04 and how to ... Read more The post How to Install and Use MySQL on Ubuntu 20.04 appeared first on RoseHosting.

Over the weekend I’ve published another issue of OpenLamp.tech, the newsletter for PHP/MySQL developers. There is plenty of great curated content for you so dive right in and enjoy! If you’re a PHP/MySQL developer, this free weekly newsletter ... [More] is for you. I’ve curated some great reads for you this week from around the web. Stories you can enjoy in this issue are: Distance querying with MySQL geospatial functions in LaravelCursors and for loops in MySQL stored proceduresGreat YouTube channel/video recommendations for learning PHPDon’t wait on me each week to repost here. Sign up and have each issue delivered directly to your inbox so you don’t miss any. The monthly deep-dive featured issue releases on the last Friday of this month/year and I’m covering MySQL Database Metadata with CodeIgniter 4. (Related: Last month’s deep-dive featured piece, 5 MySQL String Functions You Should Know, was a jam-packed issue with tons of great content so do check it out!) Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! Support my work here on this blog by tossing some spare change in my Tip Jar. Thank you so much! Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post OpenLamp.tech issue #4 appeared first on Digital Owl's Prose. [Less]

Over the weekend I’ve published another issue of OpenLamp.tech, the newsletter for PHP/MySQL developers. There is plenty of great curated content for you so dive right in and enjoy! If you’re a PHP/MySQL developer, this free weekly newsletter ... [More] is for you. I’ve curated some great reads for you this week from around the web. Stories you can enjoy in this issue are: Distance querying with MySQL geospatial functions in LaravelCursors and for loops in MySQL stored proceduresGreat YouTube channel/video recommendations for learning PHPDon’t wait on me each week to repost here. Sign up and have each issue delivered directly to your inbox so you don’t miss any. The monthly deep-dive featured issue releases on the last Friday of this month/year and I’m covering MySQL Database Metadata with CodeIgniter 4. (Related: Last month’s deep-dive featured piece, 5 MySQL String Functions You Should Know, was a jam-packed issue with tons of great content so do check it out!) Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! Support my work here on this blog by tossing some spare change in my Tip Jar. Thank you so much! Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post OpenLamp.tech issue #4 appeared first on Digital Owl's Prose. [Less]

Over the weekend I’ve published another issue of OpenLamp.tech, the newsletter for PHP/MySQL developers. There is plenty of great curated content for you so dive right in and enjoy! If you’re a PHP/MySQL developer, this free weekly newsletter ... [More] is for you. I’ve curated some great reads for you this week from around the web. Stories you can enjoy in this issue are: Distance querying with MySQL geospatial functions in LaravelCursors and for loops in MySQL stored proceduresGreat YouTube channel/video recommendations for learning PHPDon’t wait on me each week to repost here. Sign up and have each issue delivered directly to your inbox so you don’t miss any. The monthly deep-dive featured issue releases on the last Friday of this month/year and I’m covering MySQL Database Metadata with CodeIgniter 4. (Related: Last month’s deep-dive featured piece, 5 MySQL String Functions You Should Know, was a jam-packed issue with tons of great content so do check it out!) Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! Support my work here on this blog by tossing some spare change in my Tip Jar. Thank you so much! Tip Jar Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. The Newsletter for PHP and MySQL Developers How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post OpenLamp.tech issue #4 appeared first on Digital Owl's Prose. [Less]

If you are looking to improve the performance of MySQL databases in your software, you may need to know all the differences between InnoDB and MyISAM, two well-known types of MySQL storage engines. And if you are about to choose one of them, we ... [More] believe you would like to find out what each of them […] The post InnoDB vs MyISAM: A Detailed Comparison of Two MySQL Storage Engines appeared first on Devart Blog. [Less]

Connecteam is a SaaS company that provides an employee management solution for deskless teams. Over the last couple of years, we’ve been growing tremendously and we recently started to face one of the hardest technical challenges: horizontal scaling. ... [More] In the era of cloud computing, provisioning new resources is a breeze, but handling those resources in an efficient manner and providing a five nines uptime while allowing a fast-paced development environment is not an easy undertaking. [Less]

As you know, MySQL 8.0 can be used as JSON Document Store to store your documents without being linked to any schema. You can also use CRUD operations to deal with these documents using the MySQL X DevAPI. Of course in documents, it’s possible to ... [More] also store temporal attributes like date, time, datetime, … Let’s see how we can deal with such attributes. This is an example of a collection of documents with a datetime attribute createdOn: As those attributes don’t have a real type, can we use the createdOn attribute as if it was a real datetime field ? Let’s try to get all the documents have a created data > '2021-12-02': We can see that the document “dave” has been filtered out. However, we can see that “kenny” and “miguel” are also present… and this is correct as “2021-12-02 01:20:19” is indeed bigger than “2021-12-02 00:00:00“. Let’s then try with another date format : 2021-12-02 23:59:59: This is indeed what we are looking for… but can I just format the value of createdOn to just filter out using a date without the time ? You can notice that we can use datetime functions on the attribute. However not all functions are always supported in the X DevAPI: And what about performance ? Let’s run again this operation: JS> db.mycol.find("createdOn > '2021-12-02'").fields('name', 'createdOn') We can find in Performance_Schema the statement as it’s executed by MySQL and check its Query Execution Plan (using EXPLAIN): SQL> EXPLAIN SELECT JSON_OBJECT('name', JSON_EXTRACT(doc,'$.name'),'createdOn', JSON_EXTRACT(doc,'$.createdOn')) AS doc FROM `docstore`.`mycol` WHERE (JSON_EXTRACT(doc,'$.createdOn') > '2021-12-02') \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100 Extra: Using where We can notice that a full table scan is performed to execute the query. But of course, it’s again possible to optimize this by creating an index: Now if we check again the Query Execution Plan of the query, we can see that the index is used: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: range possible_keys: createdOn_idx key: createdOn_idx key_len: 6 ref: NULL rows: 3 filtered: 100 Extra: Using where But sometimes, the MySQL DBA still needs to use the good all SQL: let’s now decide that we want to use this statement and that we don’t want to change it: JS> db.mycol.find("date(createdOn) > '2021-12-02'").fields('name', 'createdOn') Could we optimize it ? Because this CRUD operation, won’t use the previously created index (due to the date()function on the attribute). That’s where the MySQL DBA will prove again all the power she/he has ! The trick here is to create a virtual column manually and then index it: SQL> ALTER TABLE mycol ADD COLUMN created_on_date date GENERATED ALWAYS AS ( date(json_unquote(json_extract(doc,_utf8mb4'$.createdOn'))) ) VIRTUAL; SQL> ALTER TABLE mycol ADD INDEX createdOn_date_idx(created_on_date); And now the index will be used: SQL> EXPLAIN SELECT JSON_OBJECT('name', JSON_EXTRACT(doc,'$.name'),'createdOn', JSON_EXTRACT(doc,'$.createdOn')) AS doc FROM `docstore`.`mycol` WHERE (date(JSON_UNQUOTE(JSON_EXTRACT(doc,'$.createdOn'))) > '2021-12-02')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mycol partitions: NULL type: range possible_keys: createdOn_date_idx key: createdOn_date_idx key_len: 4 ref: NULL rows: 1 filtered: 100 Extra: Using where As you can see, it’s easy to use Date & Time data inside JSON documents using the X Dev API with MySQL 8.0 Document Store. If needed, it’s also possible to ask the MySQL DBA to generate functional indexes on virtual columns to speed up some operations. As you may know, MySQL X Protocol is also available in MySQL Database Service on OCI. Enjoy MySQL ! [Less]