Category: mysql

MySQL Performance Boosting with Indexes and Explain

Techniques to improve application performance can come from a lot of different places, but normally the first thing we look at — the most common bottleneck — is the database. Can it be improved? How can we measure and understand what needs and can be improved? One very simple yet very useful tool is query p...

How to Optimize MySQL: Indexes, Slow Queries, Configuration

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 ...

Using MySQL with Node.js & the mysql JavaScript Client

NoSQL databases are all the rage these days and probably the preferred back-end for Node.js applications. But you shouldn’t architect your next project based on what’s hip and trendy, rather the type of database to be used should depend on the project’s requirements. If your project involves dynamic ...

How to Search on Securely Encrypted Database Fields

We [ParagonIE] get asked the same question a lot (or some remix of it). This question shows up from time to time in open source encryption libraries’ bug trackers. This was one of the “weird problems” covered in my talk at B-Sides Orlando (titled Building Defensible Solutions to Weird Problems), and w...

How to Synchronize WordPress Live and Development Databases

Developing WordPress themes is considerably easier with real content. Unexpected situations begin to arise when people add pages, posts, media and comments. Your beautiful template can break when: editors use assets which are too big or small for your breakpoints to handle managers introduce a new menu item which does...

Beaver in Action: Practical MySQL Optimization

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...

Phinx – the Migration Library You Never Knew You Needed

You’re building an application, and you need to share the database’s structure with your team. After all, you want everyone to be up and running as soon as possible. What do you do? SQL dumps of table structures? You could… but that’s so very primitive – and can be time consuming to import...

3 More Joins You Should Be Familiar With

There are many ways to JOIN data from two database tables and filter the information you require. Craig Buckler wrote a popular piece on understanding JOINs; namely INNER, LEFT, RIGHT, and FULL OUTER. This article is an extension of that one. Let’s recap these real quick. Picture two tables, one for customers and one...

Quick Tip: How to Permanently Change SQL Mode in MySQL

I was working on a legacy project recently and needed to import some data from MySQL 5.5. All the queries in the code worked perfectly in MySQL 5.5, so I assumed an upgrade to 5.7 would be seamless. Not so. First I got errors due to DateTime columns being populated with zeros during import, then when running this quer...

Learn SQL In 20 Minutes

Every web developer should know SQL. Although it has been around since the 70s, it is still widely used, and you can’t build a serious application without it. Most full-stack frameworks have libraries for dealing with the SQL complexity – ActiveRecord, Doctrine, Hibernate and more. But often times you n...