MySQL Forums :: Performance :: Group By Perfomance MySQL vs MS SQL

I have a table with a large number of records. On the order of 4 million. I have a simple query to show the grouped sum. It takes about 2 and half minutes to run in MySQL. The same query, on the same table structure takes only seconds to run in MS SQL.

Is there a way to make MySQL run as fast? I cannot possibly create all the possible combinations that might be grouped.

CREATE TABLE transactions
(
id bigint(20) unsigned NOT NULL,
date date NOT NULL ,
account_id bigint(20) unsigned NOT NULL,
amount decimal(18,2) NOT NULL ,
close_date date DEFAULT NULL,
department_id bigint(20) unsigned DEFAULT NULL,
location_id bigint(20) unsigned DEFAULT NULL,
division_id bigint(20) unsigned DEFAULT NULL,
entry_restrict_type char(3) NOT NULL,
entry_restrict_id bigint(20) unsigned NOT NULL,
entity_restrict_type char(3) DEFAULT NULL,
entity_restrict_id bigint(20) unsigned DEFAULT NULL,
entity_2_restrict_type char(3) DEFAULT NULL,
entity_2_restrict_id bigint(20) unsigned DEFAULT NULL,
void tinyint(3) unsigned NOT NULL DEFAULT '0',
lineitem_id bigint(20) unsigned DEFAULT NULL,
lineitem_prefix char(3) DEFAULT NULL,
lineitem_type tinyint(3) unsigned DEFAULT NULL,
item_class_restrict_type char(3) DEFAULT NULL,
item_class_restrict_id bigint(20) unsigned DEFAULT NULL,
entry_number bigint(20) unsigned DEFAULT NULL,
reconcile_id bigint(20) unsigned DEFAULT NULL,
cve_restrict_type char(3) DEFAULT NULL,
cve_restrict_id bigint(20) unsigned DEFAULT NULL,
reverse_id bigint(20) unsigned DEFAULT NULL,
memo text,
PRIMARY KEY (id),
KEY transactions_entry_index (entry_restrict_type,entry_restrict_id),
KEY transactions_entity_index (entity_restrict_type,entity_restrict_id),
KEY transactions_entity2_index (entity_2_restrict_type,entity_2_restrict_id),
KEY transactions_itemclass_index (item_class_restrict_type,item_class_restrict_id),
KEY transactions_cverestrict_index (cve_restrict_type,cve_restrict_id),
KEY transactions_accounts_link (account_id),
KEY transactions_department_link (department_id),
KEY transactions_division_link (division_id),
KEY transactions_location_link (location_id),
KEY transactions_reconcileid_link (reconcile_id),
KEY transactions_lineitem_index (lineitem_id,lineitem_prefix)
) ENGINE=InnoDB;

explain select sum(amount) as amount,
account_id,
division_id,
department_id,
location_id,
entity_restrict_type,
entity_restrict_id,
entity_2_restrict_type,
entity_2_restrict_id,
cve_restrict_type,
cve_restrict_id,
date
from transactions
group by account_id,
division_id,
department_id,
location_id,
entity_restrict_type,
entity_restrict_id,
entity_2_restrict_type,
entity_2_restrict_id,
cve_restrict_type,
cve_restrict_id,
date;

+----+-------------+--------------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+---------+---------------------------------+
| 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 4661399 | Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+---------+---------------------------------+



Edited 1 time(s). Last edit at 11/11/2009 03:00PM by Jim Peterson.