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

Pineco.de:
Scheduling MySQL Backups with Laravel
May 28, 2018 @ 13:19:16

On Pineco.de they've posted a new tutorial showing you how to create a MySQL database backup system using Laravel and a command-line call to mysqldump wrapped in an Artisan command.

A few days ago, I signed in to the wrong database and deleted around 18 000 records that were in use. Not to mention, we had no backup for that data. So after all, I decided to make a little script to dump the database and save it to an SQL file automatically.

But, if you are here for a fully working backup system, you should take a look at <a href="https://github.com/spatie/laravel-backup>this package. We won’t cover more just scheduling database exports and its scheduling.

The post starts with the command that will be executed on the command-line and the code required to "warm up" the console and run the command. He shares the code for this command and walks through it, explaining each part. Next comes the scheduling of the task and, as a bonus, setting up the scheduler to run on the Forge service.

tagged: schedule mysql backup laravel mysqldump tutorial

Link: https://pineco.de/scheduling-mysql-backups-with-laravel/

Sameer Borate:
How to profile and debug MySQL queries for speed
Apr 02, 2018 @ 10:40:59

On his Code Diesel site Sameer Borate has a quick post sharing a helpful tool that can make profiling the SQL queries your application makes simpler. In this post he shows how to use the Neor Profile SQL profiler desktop application (compatible with Windows, OSX and Linux) to hook into your SQL flow and evaluate the queries in real time.

Most MySQL applications need profiling for speed enhancements at one time or other. I’ve developed several web applications in the past wherein MySQL queries were a major bottleneck in the application performance. In the past however there were no easy tools to analyse SQL queries in a running application and we programmers had to depend on some makeshift solutions to debug SQL queries. Now, however there are some nice free tools which can help you profile MySQL application queries easily.

My favorite among them is Neor Profile SQL profiler. This is a desktop tool which allows you to easily profile your application SQL queries and also lets you query the same in a live application.

He starts with the changes to make to your configuration to point to the profiler for interception. This is then hooked into the MySQL database and queries will be profiled in real-time. The tool provides information like time of execution, number of rows returned, a graph showing the queries by type and includes the ability to run (or re-run) custom queries.

tagged: mysql database profile speed performance

Link: https://www.codediesel.com/mysql/how-to-profile-and-debug-mysql-queries-for-speed/

Pineco.de:
Basic Eloquent Search Techniques
Mar 28, 2018 @ 12:36:01

The Pineco.de blog has a new tutorial posted that the Laravel users out there will find particularly useful. In their latest post, they introduce some basic Eloquent searching techniques that can be used to easily locate data via currently available Eloquent functionality.

When our app is smaller – for example at the beginning – it’s enough to apply small, handmade solutions. It’s true for database searching as well. It’s not necessary to pull in a package instantly. Most of the time we can use some simple techniques to perform searches in Eloquent.

[...] Searching is a vital part of any application. A good interface helps the user to retrieve the information it needs. So it’s essential to bring a good solution both on front-end and back-end. In this post, we are not covering any UI or front-end related topics. [...] So for now, let’s talk about MySQL and Eloquent only.

In the post, they share a few methods for searching data that don't require any additional service or package. They're mostly just taking advantage of features the MySQL database supports but they're showing how to use them in a Laravel environment:

  • simple "where" clauses
  • using the fuzzy matching of the "like" keyword
  • searching JSON columns by a "path"
  • using "sounds like" to find similar values

Each item on the list comes with a few lines of code showing how to use it via an Eloquent model and a brief explanation of what's happening behind the scenes in the database.

tagged: laravel eloquent search where like json soundslike tutorial mysql database

Link: https://pineco.de/basic-eloquent-search-techniques/

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