Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

Delicious Brains:
SQL Query Optimization for Faster Sites
Nov 07, 2017 @ 09:53:47

On the Delicious Brains site they've posted a tutorial that provides some helpful tips and tricks for optimizing your SQL queries on your WordPress sites with the help of some basic techniques and software to analyze your current use.

With dynamic database-driven websites like WordPress, you might still have one problem on your hands: database queries slowing down your site.

In this post, I’ll take you through how to identify the queries causing bottlenecks, how to understand the problems with them, along with quick fixes and other approaches to speed things up. I’ll be using an actual query we recently tackled that was slowing things down on the customer portal of deliciousbrains.com.

They then walk through some of the methods for locating the queries that could be causing issues including the use of the QueryMonitor plugin and the MySQL slow query log. They provide some tips for understanding what makes the query slow and how to use the MySQL Workbench tool to determine the structure of the database and look for optimizations. Finally they get into some of the things they did to solve their own issue including adding caching, better understanding the need of the query and "thinking outside the box" with indexes and normalization.

tagged: sql query optimization performance mysqlworkbench slowquerylog mysql wordpress

Link: https://deliciousbrains.com/sql-query-optimization/

SitePoint PHP Blog:
How to Optimize MySQL: Indexes, Slow Queries, Configuration
Oct 31, 2017 @ 10:46:11

On the SitePoint PHP blog Bruno Skvorc has a post that offers some helpful advice about optimizing your MySQL database through the use of indexes, monitoring slow queries and configuration options.

MySQL is still the world’s most popular relational database, and yet, it’s still the most unoptimized – many people leave it at default values, not bothering to investigate further. In this article, we’ll look at some MySQL optimization tips we’ve covered previously, and combine them with novelties that came out since.

He starts off with the configuration changes that can be used to optimize the database, tweaking settings for Innodb pool, handling variable inspection and using a tuning tool to determine the best settings. Next up comes the look at indexes covering the different kinds first: fulltext, descending, unique/primary and regular indexes. Finally he covers some of the usual bottlenecks seen in MySQL's use in web applications, showing how to monitor for them via the slow query log.

tagged: optimize mysql database application tutorial index slowquery query configuration

Link: https://www.sitepoint.com/optimize-mysql-indexes-slow-queries-configuration/

Mohamed Said:
Laravel/MySQL JSON documents faster lookup using generated columns
Aug 22, 2017 @ 10:55:05

Mohamed Said has an interesting post to his site showing how to use computed columns for faster lookups in a MySQL database containing JSON documents.

Laravel 5.3 is shipped with built-in support for updating and querying JSON type database fields, the support currently fully covers MySQL 5.7 JSON type fields updates and lookups.

He includes an example of using the Eloquent DB handling to search for the data in the JSON and the results. He links to more information about the feature, following it up with an example of the main topic: computed columns. In it he shows how to create (on the SQL side) a generated/computed column that allows for easier extraction of the data directly from the JSON. This makes it even simpler to get the data just as you would in a normal select. He also includes an example of creating it via the Schema functionality in the database migrations.

tagged: mysql json column lookup computed generated sql data laravel tutorial

Link: https://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709

SitePoint PHP Blog:
Beaver in Action: Practical MySQL Optimization
Nov 11, 2016 @ 10:30:49

On the SitePoint PHP blog there's a tutorial posted showing how to optimize your MySQL handling with the help of the Beaver query logger package and the details it provides.

Clients with an existing application sometimes ask me to fix bugs, improve efficiency by speeding up the application, or add a new feature to some existing software. The first stage of this is researching the original code – so-called reverse engineering. With SQL databases, it is not always immediately obvious which SQL queries MySQL executed – especially if these queries were generated by a framework or some kind of external library. In this article, I will talk specifically about MySQL and present a common optimization use case which might come in handy if you run into a similar problem one day.

He shows how to update your MySQL installation to log all queries out to the log location of your choice. This log can then, in turn, be parsed by the Beaver package and provide details about what's happening in the query and where it could be optimized. The article also provides a more "real world" example of a query happening in a Yii2 application resulting in a large number of queries being generated. He shows how to update the query handling to make the loading more efficient (through joins rather than individual queries) and what the resulting statement looked like.

tagged: beaver package mysql optimization query analyze tutorial

Link: https://www.sitepoint.com/beaver-in-action-practical-mysql-optimization/

Jason McCreary:
Installing Apache, PHP, and MySQL on Mac OS X Sierra
Sep 30, 2016 @ 09:12:28

In a new post to his site Jason McCreary has posted an update to his previous "installing Apache, MySQL and PHP" post with a few changes for macOS Sierra. The changes don't make the installation too much different, however.

Mac OS X runs atop UNIX. So most UNIX software installs easily on Mac OS X. Furthermore, Apache and PHP come packaged with OS X. To create a local web server, all you need to do is configure Apache and install MySQL.

I am aware of the web server software available for Mac OS X, notably MAMP. These get you started quickly. But they forego the learning experience and, as most developers report, can become difficult to manage.

He walks you through the process of getting Apache up and running and the current version of PHP configured and working with it. He shows how to verify PHP is working as expected, how to install the DMG of MySQL and make it also work happily with the PHP installation. There's also a bit at the end you can enable to add in some additional Apache module support for mod_rewrite, mod_expires and mod_deflate.

tagged: install tutorial apache mysql macos sierra

Link: http://jason.pureconcepts.net/2016/09/install-apache-php-mysql-mac-os-x-sierra/

Johannes Schlüter:
JSON Aggregation functions
Sep 16, 2016 @ 11:53:25

Johannes Schlüter has written up an article covering an interesting feature of the latest MySQL server release related to its JSON column handling: JSON Aggregation functions.

[The JSON datatype, functions and generated columns] combined allow building very powerful applications without committing to a fixed data structure on every part, but I was missing one thing a lot: Aggregation.

A topic I'm thinking a lot about is finding ways to select nested data. In MySQL we typically have two ways for this. Either one sends multiple queries to retrieve different nesting levels or one builds JOINs which tend to deliver repetitive responses.

He points out that one solution is the GROUP_CONCAT function, but the results of this can be difficult to work with and larger data may override the small memory space allowed. Instead he introduces two new functions in v8 to help with this nested data selection: JSON_ARRAYAGG and JSON_OBJECTAGG. He provides a brief example of the use of these two functions together to replace the "concat" handling from before.

tagged: mysql json function v8 release aggregation tutorial

Link: http://schlueters.de/blog/archives/183-MySQL-8.0-JSON-Aggregation-functions.html

Johannes Schlüter:
Types in PHP and MySQL
Sep 05, 2016 @ 13:38:21

Johannes Schlüter has a post to his site detailing the handling of types in PHP and MySQL and how they might act differently than expected in some situations.

Since PHP 7.0 has been released there's more attention on scalar types. Keeping types for data from within your application is relatively simple. But when talking to external systems, like a database things aren't always as one eventually might initially expect.

He talks about MySQL types and how they relate to the "network protocol" being used, converting everything to strings. He includes a few examples of hinting on the results, one where an integer is expected/string provided and another where a string was type hinted but an integer was returned. He points out that sometimes this is a limitation of what PHP can handle, not always what MySQL returns. He also includes other examples of returning decimals - sometimes as a number value and others as a string.

This leaves the question whether you should disable the emulation in order to get the correct types. Doing this has some impact on performance characteristics: With native prepared statements there will be a client-server round-trip during the prepare and another round-trip for the execute.
tagged: types typehinting mysql database string integer decimal preparedstatement pdo

Link: http://schlueters.de/blog/archives/182-Types-in-PHP-and-MySQL.html

Master Zend Framework:
How To Build a Local Development Environment Using Docker
Sep 02, 2016 @ 11:57:05

The Master Zend Framework site has posted a tutorial helping you create a Docker-based development environment complete with PHP, MySQL and Apache working happily together.

Why in this modern day and age is setting up a development environment still such a complicated process? [...] Why is it still so hard to get one setup that works, that does what you need, and that matches the deployment environment’s of testing, staging, production and so on?

[...] By now our development environments have grown quite sophisticated. But the overhead of both building and maintaining them has increased significantly also. Wouldn’t it be easy if we could set them up, but with only a small investment of time and effort? I think you know where I might be heading with this. You can. Yes, that’s right, you can. Ever heard of Docker?

He then starts in on introducing Docker (for those not already familiar) and how it differs from a VirtualBox/Vagrant setup that's already become quite popular. He talks about "containers" and the role they play as well as an overview of the environment he's going to show you how to create. He then helps you get Docker installed, explains how the containers will work together and provides the Docker YAML configuration for each of them. The docker-compose command is then used to bring the environment up, downloading the containers as needed. The final result of his setup is a set of containers running together to serve up a Zend Framework Skeleton Application.

tagged: docker local development environment tutorial introduction mysql apache zendframework skeleton

Link: http://www.masterzendframework.com/docker-development-environment/

Matt Stauffer:
New JSON-column where() and update() syntax in Laravel 5.3
Jul 11, 2016 @ 11:03:49

In his continuing series of posts looking at the new features in Laravel 5.3, Matt Stauufer has posted this guide to the use in the new JSON column functionality MySQL now provides in recent versions.

While Laravel has had the ability to cast your data to and from JSON since version 5.0, it was previously just a convenience—your data was still just stored in a TEXT field. But MySQL 5.7 introduced an actual JSON column type.

Laravel 5.3 introduces a simple syntax for lookups and updates based on the value of specific keys in your JSON columns.

He gives an example of a simple table with a JSON column storing some metadata for the entry. He includes a bit of example content showing the full entry and related metadata and the where format for querying it. It also includes the ability to run updates on the data just like with any other where clause.

tagged: laravel jon column mysql tutorial series part5 feature

Link: https://mattstauffer.co/blog/new-json-column-where-and-update-syntax-in-laravel-5-3

Scotch.io:
Prototype Quickly in Laravel with PHP’s Built-In Server and SQLite
May 06, 2016 @ 12:20:56

The Scotch.io site has a tutorial they've posted showing how to prototype a site quickly using Laravel and its built-in server/SQLite support.

If you are a seasoned Laravel developer, you know the usual project setup drill that involves creating a new project, a fresh database, and adding a virtual host entry to Apache.

If you are starting from scratch, the Apache and MySQL installation can take some time and slow things down for you. However, I will show you how you can jump start your Laravel development without Apache and MySQL.

The tutorial shows you how to use the internal PHP server to host the application, run a Laravel site inside it and integrate SQLite as the database. Each section comes with some example code and the commands/configuration you'll need to make the system work. They also take a "deep dive" into Larvel's serve command and how it bootstraps the Laravel instance for the PHP built-in server. The post ends with a look at switching back to MySQL and a comparison of SQLite vs MySQL (as well as using SQLite for production).

tagged: prototype laravel builtin server sqlite mysql tutorial

Link: https://scotch.io/tutorials/prototype-quickly-in-laravel-with-phps-built-in-server-and-sqlite