I've been trying to get some speed improvements on a certain SELECT query. The situation is as follows: There is a (in my eyes) huge crossing table. It currently has about 20 million rows, but I expect this to grow alot. Based on this crossing table I need to create another table. To do so, I need to execute the following query:
SELECT hugeCrossingTable.field3, otherTable.field1, hugeCrossingTable.field2 * otherTable.field3 AS someName
FROM hugeCrossingTable
INNER JOIN otherTable ON hugeCrossingTable.field1 = otherTable.field2
Now this currently results in about a million rows. I already have indexes on both the field1 in the 2 tables, but it still takes 18 minutes to finish.. I thought about splitting the table, but then I'd need to find a way on how to split the data, and since it's just a crossing table nothing comes to mind on how to do this.
Any ideas on how this can be optimized?
Thanks.
On request here's the create statement:
CREATE TABLE `hugeCrossingTable` (
`field` int(11) NOT NULL,
`field1` int(11) NOT NULL,
`field2` double(10,5) DEFAULT NULL,
`field3` int(4) DEFAULT NULL,
KEY `field1` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `otherTable` (
`field` int(10) unsigned NOT NULL AUTO_INCREMENT,
`field1` int(10) unsigned NOT NULL,
`field2` int(10) unsigned NOT NULL,
`field3` decimal(5,2) NOT NULL,
PRIMARY KEY (`field`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
And the explain output:
id, select_type, table , type , possible_keys, key , key_len, ref , rows, Extra
1 , 'SIMPLE' , 'otherTable' , 'ALL', '' , '' , '' , '' , 294 , ''
1 , 'SIMPLE' , 'hugeCrossingTable', 'ref', 'field1' , 'field1', '4' , 'otherTable.field2', 69 , 'Using where'
You can't have more than 1000 columns. Your records can't be bigger than 8k each. These limits change depending on database engine.
Here are some innodb examples that work on large tables of approx. 60 to 500 million rows that demonstrate the advantages of a well designed innodb table and how best to use clustered indexes (only available with innodb)
MySQL and NoSQL: Help me to choose the right one
60 million entries, select entries from a certain month. How to optimize database?
Rewriting mysql select to reduce time and writing tmp to disk
You will also want to read the following:
http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html
http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/
Once you've sorted out your table designs and optimised your innodb config:
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
You can try something like:
start transaction;
insert into target_table (x,y) select x,y from source_table order by x,y;
commit;
Hope this helps.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With