While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query. You might not know this, but MySQL can dramatically change your query before it actually executes it. This process [...]
A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
MySQL supports two different algorithms for views: the MERGE algorithm and the TEMPTABLE algorithm. These two algorithms differ greatly. A view which uses the MERGE algorithm can merge filter conditions into the view query itself. This has significant performance advantages over TEMPTABLE views. A view which uses the TEMPTABLE algorithm will have to compute the [...]
High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work. The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]
Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.
MySQL and Sun – Oportunity for smaller companies ?
March 26, 2008 By 10 Comments
Reading Martens interview we see the quite: “As soon as the deal closed we immediately secured a big deal with a major European national police agency,” said Mickos, now SVP database products at Sun. “Key to them choosing MySQL was that we are now part of a much larger public corporation. The deal wouldn’t have [...]
MySQL VIEW as performance troublemaker
August 12, 2007 By 55 Comments
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 [...]
MySQL and PostgreSQL SpecJAppServer benchmark results
July 25, 2007 By 13 Comments
Listening to Josh Berkus presentation on OSCON today I decided to take a closer look at SpecJAppServer benchmarks results which were published by PostgreSQL recently and which as Josh Puts it “This publication shows that a properly tuned PostgreSQL is not only as fast or faster than MySQL, but almost as fast as Oracle (since [...]
Figuring out what limits MySQL Replication
March 7, 2007 By Leave a Comment
Today I was cloning the master using LVM Snapshot and found it was taking quite a while to catch up, which highlighted replication could be the limiting factor for this system quite soon, so I decided to check what is limiting MySQL Replication speed. My first idea was to check it based on slow query [...]
Derived Tables and Views Performance
August 31, 2006 By 16 Comments
Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause. In MySQL 5.0 support for views was added. These features are quite related to each other but how do they compare in terms of performance ?
SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about [...]
