Northeast PHP

August 4, 2017

Next week Northeast PHP returns to Price Edward Island and Charltottetown. Last year this event moved from Cambridge to Canada and found a wonderful audience. This year the conference is back and bigger than ever. And the local government is extremely supportive and had provided the organizers with a grant to help this year.

I will be presenting on Basic PHP Database Programming and Getting JSON Data in and Out of MySQL. Tickets are still available and there is a student discount.


MySQL 8 and Common Table Expressions – An Introduction

July 31, 2017

MySQL 8 will have Common Table Expressions also known as CTEs.  I have had people asking me when  MySQL would have CTEs for years and I am glad to report they are ready for testing.

What Does a CTE look like?

The keyword WITH is the big hint that the statement is a CTE.  For now think of a CTE as a collections of views or subqueries that you combine together for the desired results. After the WITH clause, one or more comma separated sub-clauses that are subqueries. And CTEs can reference other CTEs.  For a first example, let us combine the world_x example database’s city and country table to produce a list of all cities and their corresponding countries.

WITH
  cte1 as (select Name as CName, CountryCode as CCode from city),

  cte2 as (select Name as Name, Code from country)
SELECT cte1.CName, cte2.Name from cte1
  join cte2 on (cte1.CCode = cte2.Code)
  order by cte1.CName;

So cte1 gets all the city tables Name  and CountryCode data and cte2 grabs the country  Name and Code columns.  The CTE part of this query is in blue. The city CountryCode is a corresponding key to the country Code column.  The part of the query in green is how the CTE is queried. You could consider it a short lived temp table. And here is the abbreviated output.

+————————+———————-+
| CName               | Name              |
+————————+———————-+
| A Coruña (La Coruña) | Spain |
| Aachen              | Germany        |
| Aalborg             | Denmark        |
| Aba                    | Nigeria            |
……

Got a Simpler Example?

mysql>WITH cte as (SELECT ID, Name, District FROM city)
select Name, ID from cte limit 5;

+—————-+—-+
| Name      | ID |
+—————-+—-+
| Kabul      | 1 |
| Qandahar | 2 |
| Herat       | 3 |
| Mazar-e-Sharif | 4 |
| Amsterdam | 5 |
+—————-+—-+
5 rows in set (0.00 sec)

This second example has a CTE named cte (and the Nobel Prize for hyper creating naming goes to …). From this CTE we can then select what information we desire from it. Sp in this way it is kinda of like a View. In the above example, the CTE grabs three columns of data but from it we only take two columns. And you can name you CTE what every you want within the normal constraints.

Plus Recursive!

And CTEs can also be recursive. A recursive common table expression is a CTE with a subquery that refers to its own name. At the beginning it will proclaim WITH RECURSIVE. You will often find them used with hierarchical data such as organization charts and assembly build sheets. The following example counts down from 10

mysql> WITH RECURSIVE rcte (x) AS
(SELECT 10
UNION ALL
SELECT x – 1 FROM rcte WHERE x > 1)
SELECT * FROM rcte;
+——+
| x |
+——+
| 10 |
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+——+
10 rows in set (0.00 sec)

Where to use CTEs

CTEs are often easier to read than embedded subqueries. They disappear after the query is executed which makes them easier to manage than using temp tables.  And many think they are easier to understand than subqueries which hopefully means easier maintenance.

Performance

So lets run EXPLAIN on the first example, minus the ORDER BY:

mysql> EXPLAIN with cte1 as (select Name as CName, CountryCode as CCode from city), cte2 as (select Name as Name, Code from country) select cte1.CName, cte2.Name from cte1 join cte2 on (cte1.CCode = cte2.Code);
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 9 | world_x.city.CountryCode | 1 | 100.00 | NULL |
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\g
+——-+——+——————————————————————————————————————————————————————————————————————+
| Level | Code | Message |
+——-+——+——————————————————————————————————————————————————————————————————————+
| Note | 1003 | /* select#1 */ select `world_x`.`city`.`Name` AS `CName`,`world_x`.`country`.`Name` AS `Name` from `world_x`.`city` join `world_x`.`country` where (`world_x`.`country`.`Code` = `world_x`.`city`.`CountryCode`) |
+——-+——+——————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)

The show warnings lets us peek at the actual query the MySQL optimizer will run. The optimizer was able to rewrite the query as a fairly simple join. So in this case at least, the performance will be fine.

 

 


Phoenix This Thursday

July 10, 2017

I will be speaking in Phoenix this Thursday!

Description:
MySQL introduced a native JSON data type with version 5.7. But how do you work with JSON data (even before 5.7), what are the ‘gotchas’, and how do you integrate it with other relational data. Plus see the new MySQL document store for those who do not know Structured Query Language (SQL) but still need a top quality data store.
Schedule:

6:30pm - 6:50pm : Meet/Greet and Bring Community Questions
6:50pm - 7:00pm : Announcements
7:00pm - 8:15pm : Presentation by Dave Stokes
8:15pm - 8:30pm : Questions

DATE AND TIME
Thu, July 13, 2017
6:30 PM – 8:30 PM MST

LOCATION
Galvanize
515 East Grant Street
Phoenix, AZ 85004

Barcelona MySQL Meetup

June 20, 2017

https://www.meetup.com/Barcelona-MySQL-Meetup/events/240810931/

Upcoming Barcelona MySQL User Group Meetup,

Barcelona, Spain, July 5, 2017

It’s our pleasure to announce and invite you to the next Barcelona MySQL User Group Meetup scheduled for Wednesday, July 5, 2017. Please find more details below:

Date: July 5, 2017
Time: 19:00 – 21:00
Place: Mobile World Center, C/Fontanella 2, 08002 Barcelona, Spain (map)
Proposed agenda:
MySQL Failover and Orchestrator by Simon Mudd, the co-organizer of MySQL User Group in Madrid and database administrator of booking.com

More information & registration available at Meetup.
We are looking forward meeting you there!


May and June Schedule

May 16, 2017

The months of May and June are very active in the MySQL Community. MySQL was a sponsor and had a booth at the always lively Linuxfest Northwest. 2,500 people can’t be wrong and I had several hundred great personal talks with attendees about MySQL and databases.

Thursday night the 18th I have a short talk on Nomad PHP on MySQL & JSON. This is an online PHP User Group and attracts some of the best in the PHP world (not sure how I got in there).

The always awesome PHPTek moves to Atlanta and I will present a three hour workshop on MySQL Replication 101 — Bring a laptop with Docker loaded and we will go from setting up basic master/slave replication to building a three node Group Replication cluster. This is the show on the PHP conference circuit that even log time PHP developers walk away with a skull full of new knowledge and a stack of new things to try.

Then to Charlotte to Southeast Linuxfest for three presentations. This is a fun event with a warm, friendly audience. There is a Zero to DBA track which is part of Oracle’s Diamond Sponsorship of the show. I will talk on MySQL 8, JSON Data and MySQL, and A Brief History of the Weird Side of Computing. This event has a wild set of evening activities for those who choose to partake.

Then it is off to Scotland to the UK Oracle User Group where I will be speaking on JSON and MySQL Best Practices.


Storage Engine Vendor Meeting

April 30, 2017

It was my good fortune to host a Storage Engine Vendor Meeting this past week. With very short notice, we had representatives from several companies who graciously arose early during the Percona Live conference. This was an opportunity to touch base with these vendors and put faces with names in a few cases. Several MySQL engineers were on hand to answer specific questions and those of us from MySQL received updates from the vendors. The MySQL Community is very fortunate to have so many talented folks contributing to it from these vendors.


The London MySQL Meetup

April 25, 2017

The various MySQL User Groups or MySQL Meetups around the world are made of up of various groups of individuals that all have a passion for MySQL. Some are large and intense like San Francisco or Boston. Others are fun loving like Chicago or obviously vary hard working like Oklahoma City. I recommend for those of you that travel to connect with these groups. But there is one group that is a little special. They meet in a pub, upstairs in the back. There is a bar and bartender. The food is very good and the attendees very sharp. If you get a chance, visit the London MySQL Meetup

I had been in York a few days before and was invited by Ivan Zoratti, a long time institution of the MySQL Community and Meetup organizer to present on MySQL 8. I made it to The Lamb, a pub on Conduit street and made my way to the meeting room. I was greeted by the bartender (a nice addition to your meeting if you can arrange it) and soon the room filled. I gave my presentation and we then started a fun question & answer period.

All too soon we realized it was getting very late and had to disband. But I had a wonderful time and decided that I need to make a return visit.

So if you find yourself in London, please take the effort to try to attend one of the London MySQL Meetup meetings.