November « 2010 « Alexander Rubin's Blog on MySQL

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) [...]

Converting queries with

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
where dayofweek = [...]

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 tables [...]

Alexander Rubin's Blog on MySQL

Categories

Archives

Fixing data warehousing queries with group-by

Converting queries with

Loose index scan vs. covered indexes in MySQL

Why mysqldump is converting my tables from InnoDB to MyISAM?

Pages

Blogroll