I have the below query that takes about 5 seconds to execute on MySQL 4.0.18 with Windows 2000 with 512 MB RAM.
select HIGH_PRIORITY INET_NTOA(Table1.IP_COLUMN1) as COLA, INET_NTOA(Table1.IP_COLUMN2) as COLB, Table2.COLUMN1 as COLC, Table2.COLUMN2 as COLD, Table2.COLUMN3 as COLE, sum(Table1.COLUMN3) as n, Table2.COLUMN4 as COLF from Table1, Table2 where Table1.COLUMN5 = Table2.COLUMN5 and Table1.COLUMN5 = '37' and TIME between 20041130101200 and 20041201101200 group by COLA, COLB, COLC, COLD, COLE order by n desc LIMIT 100
Table1 (a MyISAM table) has about 1 million records and Table2 (InnoDB) has about 1500 records. Table1.COLUMN5='37' is satisfied by about 10000 rows. Table1 gets about 30000 records once every 3 minutes and this is loaded from a file using a "load data infile....". I have been able to reduce this "load data infile..." to about 2 seconds but my select queries (which is what I want to be really fast) have actually become slower after I changed the start up parameters as below: