Insight for Developers

The MySQL optimizer, the OS cache, and sequential versus random I/O

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 another, and the optimizer chose […]

Read more

Speeding up GROUP BY if you want aproximate results

Doing performance analyzes today I wanted to count how many hits come to the pages which get more than couple of visits per day. We had SQL logs in the database so It was pretty simple query:

Shell

select sum(cnt) from (select count(*) cnt from performance_log_080306 group by page having cnt>2) pv;

1

select sum(cnt) from (select […]

Read more