'm using or trying to use mysql in my development machine, Core 2 Duo with 2GB of ram.
I'm queryin three tables, that are bout 150000, 60000 and 15000 rows.
The query goes like this:
(Select Count(*) From table1 X Where A.Rowidentifier = X.Rowidentifier
) As ColX,
(Select Count(*) From table2 X Where A.Rowidentifier = X.Rowidentifier
And column2 = 'AD') As ColY,
(Select Count(*) From table3 X Where A.Rowidentifier = X.Rowidentifier
) As ColZ
From table3 A
Where identifier = 1
And project = 4
And event >= '2007-02-20'
I have indexes on all the columns involved. MySQL returns that it will use indexes when I run the explain.
Well, this query takes 10 minutes to get me 1100 rows of data.
Fedup of MySQL I migrated this data to SQL Server (MSDE). It took 5 seconds WITHOUT INDEXES.
When I added indexes it took >1.
The MySQL buffer pool is 512MB. Key buffer is 512MB.
I tried with both MyISAM and InnoDB tables, and both takes 10-15 minutes to return. The only difference is that InnoDB seems more gently with my harddisk and cpu. While MyISAM makes the computer slow.