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

Joe Ferguson:
Adding MySQL 8 support to Laravel Homestead
Nov 07, 2018 @ 12:57:21

In a new post to his site Joe Ferguson has included a screencast showing how to add MySQL 8 support to Laravel Homestead for your local development.

My friend Beau Simensen has been doing awesome stuff building and streaming his work on astrocasts.com. He’s inspired me to start streaming again and last night I spent some time adding a feature to Laravel Homestead to add MySQL 8 as an option.

In the video Joe walks through the whole process including how Homestead is set up (via Vagrant) and all of the configuration changes you'll need to make to get MySQL 8 support up and running. The video runs about an hour and a half but it's a great resource if you're looking to use this latest version of MySQL in your application.

tagged: screencast mysql8 mysql support homestead tutorial

Link: https://www.joeferguson.me/adding-mysql-8-support-to-laravel-homestead/

Stitcher.io:
Eloquent MySQL views
Aug 28, 2018 @ 10:38:54

On the Sticher.io blog Brent has written up a post covering the use of MySQL views in Eloquent, the database ORM that's included with the Laravel framework.

MySQL views are a way of storing queries on the database level, and producing virtual tables with them. In this post we'll look at why you want to use them and how they can be integrated in Laravel with Eloquent models.

If you're already convinced of the power of MySQL views, or just want to know how to implement them in Laravel, you're free to skip ahead.

For those not familiar with the concept of "views" in MySQL, he spends a little time explaining what they are and what benefits they bring to the table. This includes a code example of a migration to create one and how something similar could be achieved with event hooks on a Laravel model. He then gets into the use of the views with Laravel, refactoring a more complex SELECT query into a view and creating/removing it using the same migration methods as any other table in the database.

tagged: eloquent mysql view tutorial introduction database laravel

Link: https://stitcher.io/blog/eloquent-mysql-views

php[architect]:
MySQL Without The SQL - Oh My!
Jul 13, 2018 @ 10:29:17

On the php[architect] site they've posted a full article from their July 2018 issue sharing information about a new kind of data store in MySQL.

Do you work on projects where you begin coding before knowing what your data looks like? Or are you part of the vast majority of developers who have had little or no training in database theory, relational calculus, Structured Query Language, or sets? Could you be working on a project without a database administrator to set up relational tables, indexes, and schemas? Or are you tired of embedding ugly lines of SQL in your pristine PHP code? There is new hope for you.

In the article Dave Stokes (of MySQL/Oracle) covers the new native JSON data type that was added in MySQL 5.7 but enhanced in MySQL 8 as a document store. He shows how to use this new functionality from the command line, what a "document" is and how to install and use the X DevAPI PECL extension to make use of it from your PHP code.

tagged: mysql datastore json tutorial pecl extension

Link: https://www.phparch.com/2018/07/mysql-without-the-sql-oh-my/

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/