I originally posted this thread in newbie but on the suggestion of someone else, I have
cross-posted here for help.
>Post #1
Hello, I need to combine two queries into one query. Here are the basics.
select t3.id , t4.id , (select * from t1 , t2 where t1.id = t3.id and t2.id = t4.id) , [more variables] from t3 , t4 where [lots of stuff].
If I run query 1 and query 2 alone the response times are .13 and .43 seconds, respectively. However, combined into one query the response time is a whopping 7 plus minutes. Any ideas on why this is happening. I have included the two queries.
Query 1:
select gl2.debit from general_ledger gl2 , invoices i2 where ( ( i2.posted = 'true' and gl2.general_ledger_id = 222 and gl2.type = 'Invoiced Transfer' ) or ( i2.posted = 'false' and gl2.general_ledger_id = 131 and gl2.type ='Prepayment' ) ) and gl2.parent_id = i2.id and i2.date_posted <= '2003-01-31 23:59:59';
Query 2:
select distinct i.id , gl.id , i.reference_number , if( gl.credit > 0 , gl.credit , - gl.debit ) as amount , round( ( unix_timestamp( '2005-08-01 23:59:59' ) - unix_timestamp( i.date_posted ) ) / 86400 ) as elapsed , gl.date from location l left join company c on l.company_id = c.id left join countries co on l.country = co.id , location_connection lc , invoices i , general_ledger gl WHERE l.accounting_type = 'Vendor' and lc.location_id = '37' and lc.invoice_id = i.id and i.posted = 'true' and gl.posted = 'true' and gl.parent_id = i.id and ( gl.type ='Invoice Net' || gl.type = 'Prepayment' ) and ( lc.type = 'Invoice To' || lc.type = 'Bank Transfer' ) and gl.gl = '5' and i.date_posted <= '2003-01-31 23:59:59' ORDER BY c.name , l.city , gl.date limit 11;
Query 3:
select distinct i.id , gl.id , i.reference_number , if( gl.credit > 0 , gl.credit , - gl.debit ) as amount , ( select gl2.debit from general_ledger gl2 , invoices i2 where ( ( i2.posted = 'true' and gl2.general_ledger_id = gl.id and gl2.type = 'Invoiced Transfer' ) or ( i2.posted = 'false' and gl2.general_ledger_id = i.id and gl2.type ='Prepayment' ) ) and gl2.parent_id = i2.id and i2.date_posted <= '2003-01-31 23:59:59' ) as payment , round( ( unix_timestamp( '2005-08-01 23:59:59' ) - unix_timestamp( i.date_posted ) ) / 86400 ) as elapsed , gl.date from location l left join company c on l.company_id = c.id left join countries co on l.country = co.id , location_connection lc , invoices i , general_ledger gl WHERE l.accounting_type = 'Vendor' and lc.location_id = '37' and lc.invoice_id = i.id and i.posted = 'true' and gl.posted = 'true' and gl.parent_id = i.id and ( gl.type ='Invoice Net' || gl.type = 'Prepayment' ) and ( lc.type = 'Invoice To' || lc.type = 'Bank Transfer' ) and gl.gl = '5' and i.date_posted <= '2003-01-31 23:59:59' ORDER BY c.name , l.city , gl.date limit 11;
Thanks.
Brian
>Posted #2
Felix Geerinckx wrote:
> 0) 'SELECT VERSION();'
| version() |
+----------------------+
| 4.1.10a-standard-log |
> 1) 'SHOW CREATE TABLE <table> \G' for all
> tables involved
| general_ledger | CREATE TABLE `general_ledger` (
`id` int(10) NOT NULL auto_increment,
`parent_id` int(10) default '0',
`location_id` int(10) default '0',
`transaction_number` varchar(15) default '',
`quantity` int(5) default '0',
`debit` float(11,2) default '0.00',
`credit` float(11,2) default '0.00',
`description` varchar(255) default '',
`gl` int(10) default '0',
`journal` varchar(5) default '',
`type` varchar(25) default '',
`posted` enum('true','false') default 'false',
`general_ledger_id` int(10) default '0',
`sort_order` int(3) default '0',
`date` datetime default NULL,
PRIMARY KEY (`id`),
KEY `index1` (`parent_id`,`location_id`,`gl`,`general_ledger_id`),
KEY `index2` (`gl`,`description`(20))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| location | CREATE TABLE `location` (
`id` int(10) NOT NULL auto_increment,
`company_id` int(10) default NULL,
`address` varchar(100) default NULL,
`city` varchar(50) default NULL,
`state` varchar(50) default NULL,
`zipcode` varchar(15) default NULL,
`country` int(3) default NULL,
`origin` varchar(50) default 'NA',
`port_id` int(10) default NULL,
`accounting_type` enum('Vendor','Client') default NULL,
`joined` datetime default NULL,
PRIMARY KEY (`id`),
KEY `index1` (`company_id`,`country`,`port_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| company | CREATE TABLE `company` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`credit` int(10) default '0',
`interest_rate` float(8,2) default '10.00',
`method` int(10) default '1',
`website` varchar(255) default '',
`deals` int(4) default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| countries | CREATE TABLE `countries` (
`id` int(3) NOT NULL auto_increment,
`country` varchar(50) default NULL,
`region` int(10) default '0',
PRIMARY KEY (`id`),
KEY `index1` (`region`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| location_connection | CREATE TABLE `location_connection` (
`id` int(10) NOT NULL auto_increment,
`invoice_id` int(10) default '0',
`location_id` int(10) default '0',
`contact_id` int(10) default '0',
`type` varchar(25) default NULL,
PRIMARY KEY (`id`),
KEY `index1` (`invoice_id`,`location_id`,`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| invoices | CREATE TABLE `invoices` (
`id` int(10) NOT NULL auto_increment,
`reference_number` varchar(20) default NULL,
`status` enum('Active','Backup','Archive') default 'Active',
`type` varchar(50) default '',
`deal_id` int(10) default '0',
`balance` float(17,2) default '-1000000000.00',
`posted` enum('true','false') default 'false',
`finished` enum('Yes','No') default 'Yes',
`reconciled` enum('Yes','No','Void') default 'No',
`reconciled_date` datetime default NULL,
`cancelled_sale` enum('Yes','No') default 'No',
`aq_order` int(3) default '0',
`date` datetime default NULL,
`date_posted` datetime default NULL,
PRIMARY KEY (`id`),
KEY `index1` (`deal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
> 2) 'SHOW INDEX FROM <table> \G' for alltables involved
Indexes are shown with the table creations above.
> 3) 'EXPLAIN <query> \G' for the three queries
This is for aged-payables/receivables. I am find all of a clients invoices to a date and then what they have paid on those invoices up to the date. Query 1 finds what they have paid on the invoices. Query 2 finds what invoices they have. The combined shows the balance on each invoices to date.