Proper MySQL Query Optimization starts with a proper Slow Query Logging session. And MySQL Query Optimization is where I spend 70-80% of my time when doing MySQL performance optimization. In this short series I will show you how to do Slow Query Logging the right way.

Here’s links to the other two parts:

• Advanced MySQL Slow Query Logging Part 2: pt-query-digest report
• Advanced MySQL Slow Query Logging Part 3: fine-tuning the logging process

The post Advanced MySQL Slow Query Logging appeared first on Speedemy.

Last week, 21-23 September, it took place the European MySQL Conference, or “Data performance Conference” as this year’s subtitle was “MySQL. NoSQL. Data in the cloud.”. This year, it changed its location from London to Amsterdam and, as most people I talked to agreed, the change was for good. As every year, Percona was the company organizing it, but it had the participation of all the major players in the open source MySQL/MongoDB/Cloud data world. Special mention goes to Booking.com, which had more …

Users are complaining about slowness in your system, MySQL load is always high… The more your database has access, the more it may get slow or worse: slowness even if it is running with low load. You are starting to get desperate! The consequences of slowness and high load are disastrous: If your site is slow,... Read More

The post MySQL Performance and Tuning Best Practices appeared first on Devops for Dummies.

If you install a MySQL Database Server with the default options, your data are insecure and your server is in risk of invasion and some performance issues will appear shortly. With some best practices, your MySQL database becomes secure and the performance goes well. 1 – Set root password and change its login name As... Read More

The post MySQL Security Best Practices appeared first on Devops for Dummies.

When I came from Linux world to Mac OS, I’ve got frustrated in not having a package management system as there are in Ubuntu (with Apt-get) or in CentOS ( YUM ). The world was so fantastic, if I need to install MySQL Database on Ubuntu, I’d just type: $ sudo apt-get install mysql-server On Mac, the […]

How can this be? I am shocked. I have looked at query plans, confirmed indexes, checked handler status variables after query execution to figure out what MySQL is up to, and I don’t think there is anything wrong with it. MySQL is using the right index, using ICP, Batched Key Access. Basically, everything that we can throw at it. I even tried MariaDB and it used the new Batched Hash Join. Same result. Postgres is done in 150ms and MySQL 5.6 takes 3s!

We had a customer who was migrating from Postgres to MySQL approach us about a slow running query. Here’s the situation. They have a fleet of cars, which are loaned out to customers for short periods of time. The cars have sensors that report mileage periodically. They want to figure out which customer drove how many miles during a certain time period. Easy enough, right?

The cust_car table (25K rows) captures which customer had which car and what was the odometer reading when it was …

Join 8000 others and follow Sean Hull on twitter @hullsean. SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code. Here’s a few quick tips to write tighter queries in MySQL 1. Get rid of those Subqueries! Subqueries are a standard part of SQL, unfortunately […]

The post 3 Simple Patterns for Tighter MySQL Code appeared first on Scalable Startups.

Join 6100 others and follow Sean Hull on twitter @hullsean. Lots and lots of web applications need to page through information. From customer records, to the albums in your itunes collection. So as web developers and architects, it’s important that we do all this efficiently. Start by looking at how you’re fetching information from your [...]

Join 6100 others and follow Sean Hull on twitter @hullsean. There are two ways to speedup UNIONs in a MySQL database. First use UNION ALL if at all possible, and second try to push down your conditions. [mytweetlinks] 1. UNION ALL is much faster than UNION How does a UNION work? Imagine you have two [...]

Calculating the standard deviation in MySQL is a no-brainer by using the build-in aggregate function STDDEV(). If you don't need the original data and only want to save aggregated values in your database, the whole matter is getting more complicated - but is worth from a space and performance point of view.

Read More »