November 2010
« Apr   May »

Fixing data warehousing queries with group-by

With the standard data warehousing queries we have a fact table and dimension tables and we join them. For example, the fact table (Table size: 5M rows, ~2G in size) from my previous Loose index scan vs. covered indexes in MySQL post:

CREATE TABLE `ontime_2010` ( `YearD` int(11) DEFAULT NULL, `MonthD` tinyint(4) DEFAULT NULL, `DayofMonth` […]

Converting queries with OR to Union to ulitize indexes

Lets say we have a table storing mail messages and we need to show user’s mailbox: messages sent “from” and “to” the specified user.

Here is our table:

CREATE TABLE `internalmail` ( `mail_id` int(10) NOT NULL AUTO_INCREMENT, `senderaddress_id` int(10) NOT NULL, `recipientaddress_id` int(10) NOT NULL, `mail_timestamp` timestamp NULL DEFAULT NULL, … message body, etc … […]

Loose index scan vs. covered indexes in MySQL

Loose index scan in MySQL can really help optimizing “group by” queries in some cases (for example, if you have only min() and/or max() as your aggregate functions). For example, if you have this query (to find maximum delay for all US flights with departure on Sundays in 2010):

select max(DepDelayMinutes), carrier, dayofweek from ontime_2010 […]

Why mysqldump is converting my tables from InnoDB to MyISAM?

First of all: mysqldump is not converting tables. It is something else. Here is the story:

One of my clients had a case when they were migrating to a new mysql server: they used mysqldump to export data from the old server (all InnoDB) and imported it to the new server. When finished, all the […]