October 22, 2014

How expensive is a WHERE clause in MySQL?

This is a fun question I’ve been wanting to test for some time.  How much overhead does a trivial WHERE clause add to a MySQL query?  To find out, I set my InnoDB buffer pool to 256MB and created a table that’s large enough to test, but small enough to fit wholly in memory:

Four ways to optimize paginated displays

A paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular lists are good examples. You know the design pattern: display 20 results in some most-relevant order. Show a “next” and “previous” link. And usually, show how many items are in the whole list […]

Is disk Everything for MySQL Performance ?

I read very nice post by Matt today and it has many good insights though I can’t say I agree on all points. First there is a lot of people out where which put it as disk is everything. Remember Paul Tuckfield saying “You should ask how many disks they have instead of how many […]

MySQL Optimizer team comments on TPC-H Results

Yesterday I had a chance to speak to Igor – head of MySQL optimizer team and Timur – both of them expressed concern with TPC-H run results I posted and notes about little gains in MySQL 6.0. Do not get this post wrong. I’m not saying MySQL 6.0 SubQuery optimizations are non existent or priorities […]

TPC-H Run on MySQL 5.1 and 6.0

We were doing MySQL Performance evaluation on TPC-H queries for the client and they kindly allowed us to publish results which are very interesting. This is obviously not audited TPC-H run, and it can’t be because we used MyISAM tables which are not ACID complaint. Plus we only measured Power to keep things simple. We […]

MySQL VIEW as performance troublemaker

I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries – to keep queries simple without really thinking how it affects server performance. Even worse than that – looking at the short table which just gets single row […]

Implementing efficient counters with MySQL

On many web sites you would see a counter how many time given object – blog post, forum thread, image, movie etc was viewed. This is sometimes handy feature but it can be rather expensive from performance point of view. The nasty thing with counters as they are implemented the most trivial way – they […]

Top 5 Wishes for MySQL

About a week ago Marten send me email pointing to his article published on Jays Blog (Come on Marten, it is time for you to get your own blog). I should have replied much earlier but only found time to do that now. So here is my list 1. Be Pluggable Unlike many OpenSource projects […]

Performance impact of complex queries

What is often underestimated is impact of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries which were taking milliseconds to stall for few seconds, especially in certain OS configurations, and on low profile servers (ie having only one disk drive) […]

Feature Idea: Finding columns which query needs to access

In query examinations it is often interesting which columns query needs to access to provide result set as it gives you ideas if you can use covering indexes to speed things up or even cache some data by denormalizing tables. So far it has to be done manually – look at SELECT clause, WHERE clause, […]