by Maciej Dobrzanski | May 17, 2010 | Insight for Developers, MySQL
The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last...
by Morgan Tocker | Jan 9, 2010 | Insight for Developers, MySQL
There was a discussion on LinkedIn one month ago that caught my eye: Database search by “within x number of miles” radius? Anyone out there created a zipcode database and created a “search within x numer of miles” function ? Thankful for any...
by Baron Schwartz | Sep 29, 2009 | MySQL
Recently I was doing a little work for a client who has MyISAM tables with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty...
by Peter Zaitsev | Sep 20, 2009 | Insight for Developers, MySQL
I spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last 10 years I chased first Monty and later Igor with Optimizer complains and suggestions. Here are some general ideas which I...
by Peter Zaitsev | Sep 19, 2009 | Insight for DBAs, MySQL
The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND...
by Baron Schwartz | Aug 16, 2009 | Benchmarks
Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a “roughly equivalent” subquery. The idea — and there may be shortcomings that are poisoning the results here,...
by Baron Schwartz | Mar 5, 2009 | Insight for Developers
If you were interviewing to work at Percona, and I asked you “what does Using filesort mean in EXPLAIN,” what would you say? I have asked this question in a bunch of interviews so far, with smart people, and not one person has gotten it right. So I...
by Baron Schwartz | Jan 23, 2009 | Insight for Developers
How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they’re all the same and not calculate the result for each of them? I had a specific case where I needed to find out for sure, so I made a little benchmark. The query...
by Baron Schwartz | Dec 22, 2008 | Insight for Developers
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....
by Baron Schwartz | Oct 31, 2008 | Benchmarks, Insight for DBAs
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...
by Peter Zaitsev | Aug 1, 2008 | Insight for Developers
JOINs are expensive and it most typical the fewer tables (for the same database) you join the better performance you will get. As for any rules there are however exceptions 🙂 The one I’m speaking about comes from the issue with MySQL optimizer stopping using...
by Baron Schwartz | Apr 28, 2008 | Insight for Developers
In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario. In short, a query that could take several days to run with one join order takes an hour with...
by Baron Schwartz | Apr 22, 2008 | Insight for DBAs
Have you ever run a query in MySQL and wondered how long it’ll take to complete? Many people have had this experience. It’s not a big deal until the query has been running for an hour. Or a day and a half. Just when IS that query going to finish, anyway?...
by Vadim Tkachenko | Mar 25, 2008 | Benchmarks
Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries...
by Peter Zaitsev | Oct 17, 2007 | Insight for Developers
One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column...
by Peter Zaitsev | Oct 5, 2007 | Benchmarks, Insight for Developers
When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having...
by Peter Zaitsev | Sep 18, 2007 | Insight for Developers
Every so often you need to perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to still use second portion of the index for the order by. Ranges as well as IN lists make this optimization impossible,...
by Vadim Tkachenko | Aug 28, 2007 | Benchmarks, Insight for DBAs
About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I’m speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as...
by Peter Zaitsev | Apr 10, 2007 | Insight for Developers
Looking at how people are using COUNT(*) and COUNT(col) it looks like most of them think they are synonyms and just using what they happen to like, while there is substantial difference in performance and even query result. Lets look at the following series of...
by Peter Zaitsev | Apr 6, 2007 | Insight for Developers
In many Search/Browse applications you would see main (fact) table which contains search fields and dimension tables which contain more information about facts and which need to be joined to get query result. If you’re executing count(*) queries for such result...
