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 »

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 »

One of my colleague asked me : « Is an ALTER TABLE with many specifications is faster than 1 ALTER TABLE by specification ? »

The answers seems quite intuitive, anyway figures are better than a long speech...

Context :

MySQL Community Server 5.5.22

InnoDB table with 8 539 238 rows.

17 columns with INT, CHAR, VARCHAR, TEXT, DATETIME, … fields

You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.

Read More »

You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.

Read More »

The first annual Latin America MySQL/NoSQL/Cloud Conference was held in Buenos Aires Argentina from June 26-28. Kudos to Santiago Lertora from Binlogic who had the vision for the conference in his country and made it happen. I look forward to the second annual event.

My first presentation was “Improving Performance with Better Indexes”. This presentation details the six steps to SQL performance analysis, Capture, Identify, Confirm, Analyze, Optimize and Verify. An explanation of MySQL EXPLAIN, and working examples to create indexes and better covering indexes in several examples are provided. A production example of a 13 table join is used to detail how covering indexes and partial column indexes can make a dramatic improvement in performance. …