Optimized query still way too slow
Hi,
I've got a query with four joins between two tables (each joined twice), and it's performing like a dog. I've been through the query optimisation reference, and have explained the query and confirmed it's using the correct indexes, but it's still taking > 3mins to return 3000 rows.
Any help would be very appreciated, as I'm new at MySql optiisation.
Here are the table defs:
Create table Reconciliation_Batch (
Id Int UNSIGNED NOT NULL AUTO_INCREMENT,
Org_Id Int UNSIGNED NOT NULL,
Role Tinyint UNSIGNED NOT NULL,
Peer_Org_Id Int UNSIGNED NOT NULL,
Status Tinyint UNSIGNED NOT NULL,
Invoice_File_Id Int UNSIGNED,
Active Bit(1) NOT NULL,
Created_At Datetime NOT NULL,
Created_By Int UNSIGNED NOT NULL,
Updated_At Datetime,
Updated_By Int UNSIGNED,
Lock_Version Int UNSIGNED NOT NULL,
Primary Key (Id)) ENGINE = InnoDB
DEFAULT CHARACTER SET UTF8;
Create table Reconciliation_Batch_Data (
Id Int UNSIGNED NOT NULL AUTO_INCREMENT,
Batch_Id Int UNSIGNED NOT NULL,
Status Tinyint UNSIGNED NOT NULL,
Ref Varchar(15) NOT NULL,
Document_Id Varchar(20),
Currency Char(3) NOT NULL,
Amount Decimal(12,5),
Travel_Date Date NOT NULL,
Passenger_Name Varchar(100) NOT NULL,
Active Bit(1) NOT NULL,
Created_At Datetime NOT NULL,
Created_By Int UNSIGNED NOT NULL,
Updated_At Datetime,
Updated_By Int UNSIGNED,
Lock_Version Int UNSIGNED NOT NULL,
Primary Key (Id)) ENGINE = InnoDB
DEFAULT CHARACTER SET UTF8;
There query relies on the indexes created automatically for the primary & foreign keys between Reconciliation_Batch<->Reconciliation_Batch_Data, and Reconciliation_Batch->Organisation on the Org_Id and Peer_Org_Id columns.
I have also created an index for all the columns where Reconciliation_Batch_Data joins to itself:
Create Index Reconciliation_Batch_Data_Matching ON Reconciliation_Batch_Data (Status,Ref,Document_Id,Currency,Amount,Travel_Date,Passenger_Name);
Here is the query:
select reconcilia0_.id as id34_0_, reconcilia1_.id as id34_1_, reconcilia0_.lock_version as lock2_34_0_, reconcilia0_.active as active34_0_, reconcilia0_.created_at as created4_34_0_, reconcilia0_.created_by as created15_34_0_, reconcilia0_.updated_at as updated5_34_0_, reconcilia0_.updated_by as updated13_34_0_, reconcilia0_.ref as ref34_0_, reconcilia0_.status as status34_0_, reconcilia0_.Currency as Currency34_0_, reconcilia0_.Amount as Amount34_0_, reconcilia0_.Batch_Id as Batch14_34_0_, reconcilia0_.Document_Id as Document10_34_0_, reconcilia0_.Passenger_Name as Passenger11_34_0_, reconcilia0_.Travel_Date as Travel12_34_0_, reconcilia1_.lock_version as lock2_34_1_, reconcilia1_.active as active34_1_, reconcilia1_.created_at as created4_34_1_, reconcilia1_.created_by as created15_34_1_, reconcilia1_.updated_at as updated5_34_1_, reconcilia1_.updated_by as updated13_34_1_, reconcilia1_.ref as ref34_1_, reconcilia1_.status as status34_1_, reconcilia1_.Currency as Currency34_1_, reconcilia1_.Amount as Amount34_1_, reconcilia1_.Batch_Id as Batch14_34_1_, reconcilia1_.Document_Id as Document10_34_1_, reconcilia1_.Passenger_Name as Passenger11_34_1_, reconcilia1_.Travel_Date as Travel12_34_1_ from Reconciliation_Batch_Data reconcilia0_, Reconciliation_Batch_Data reconcilia1_, Reconciliation_Batch reconcilia2_, Reconciliation_Batch reconcilia3_ where reconcilia0_.Batch_Id=reconcilia2_.id and reconcilia1_.Batch_Id=reconcilia3_.id and reconcilia0_.Batch_Id=2 and reconcilia2_.Org_Id=reconcilia3_.Peer_Org_Id and reconcilia2_.Peer_Org_Id=reconcilia3_.Org_Id and reconcilia2_.role<>reconcilia3_.role and reconcilia0_.ref=reconcilia1_.ref and reconcilia0_.Document_Id=reconcilia1_.Document_Id and reconcilia0_.Amount=reconcilia1_.Amount and reconcilia0_.Currency=reconcilia1_.Currency and reconcilia0_.Travel_Date=reconcilia1_.Travel_Date and reconcilia0_.Passenger_Name=reconcilia1_.Passenger_Name and reconcilia0_.status=0 and reconcilia0_.status=reconcilia1_.status
Edited 1 time(s). Last edit at 08/13/2007 04:55PM by Brad Douglas.