September 25th, 2012, 03:00 PM
Slow 'NOT IN' query
I am suffering today with slow queries where there are a lot of records matching. I suspect it may be something to do with my 'NOT IN' operator, but not sure how to resolve.
SELECT SUM(amount) as amount FROM transactions WHERE account_id = 'L1095'
AND (trans_date > '2012-08-23 13:37:00' OR (rent_run_id > '2012-08-23 13:37:00' AND trans_type = 5001))
AND rent_run_id < NOW() AND trans_type = 5001 AND amount > 0
AND amount NOT IN (SELECT amount FROM transactions WHERE account_id = 'L1095' AND rent_run_id > NOW() AND trans_type = 5001)
September 25th, 2012, 06:26 PM
or something like that.
SELECT SUM(tx.amount) amount
FROM transactions tx
JOIN transactions ty
ON ty.account_id = tx.account_id
AND ty.trans_type = tx.trans_type
AND ty.rent_run_id > NOW()
AND ty.amount = tx.amount
WHERE tx.account_id = 'L1095'
AND (tx.trans_date > '2012-08-23 13:37:00' OR (rent_run_id > '2012-08-23 13:37:00'))
AND tx.rent_run_id < NOW()
AND tx.trans_type = 5001
AND tx.amount > 0
AND ty.account_id IS NULL;
If it's still slow, and assuming the right results are returned, consider providing the EXPLAIN for the query, as well as a CREATE TABLE statement for the transactions table.
September 25th, 2012, 07:02 PM
Unfortunately that query was slightly slower
'1', 'SIMPLE', 'tx', 'ALL', 'account_id', NULL, NULL, NULL, '27425', 'Using where'
'1', 'SIMPLE', 'ty', 'ALL', 'account_id', NULL, NULL, NULL, '27425', 'Using where; Not exists'
CREATE TABLE `transactions` (
`idtrans` int(11) NOT NULL AUTO_INCREMENT,
`account_id` varchar(45) NOT NULL,
`tenant_id` varchar(45) DEFAULT NULL,
`trans_id` int(11) NOT NULL,
`job_id` int(11) DEFAULT NULL,
`amount` decimal(8,2) NOT NULL DEFAULT '0.00',
`trans_date` datetime DEFAULT NULL,
`trans_period` varchar(16) DEFAULT NULL,
`rent_run_id` date DEFAULT NULL,
`trans_type` varchar(45) DEFAULT NULL,
`payment_ref` varchar(45) DEFAULT NULL,
`payment_type` int(11) NOT NULL DEFAULT '6',
`recon_date` date DEFAULT NULL,
`recon_stat_no` varchar(45) DEFAULT NULL,
`recon_id` int(11) NOT NULL DEFAULT '0',
`recalled` smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`idtrans`),
FULLTEXT KEY `account_id` (`account_id`)
) ENGINE=MyISAM AUTO_INCREMENT=62353 DEFAULT CHARSET=latin1$$
September 26th, 2012, 01:37 AM
Do many of your queries use MATCH/AGAINST or CONTAINS on account_id?
Can account_id really be 45 characters in length?
September 26th, 2012, 02:56 AM
In the main account_id are 5-6 digits. I suppose I could simplify the tables by having a column for each of the mostly used account types, which sre integers. I have also just noticed a couple of columns should be intergers and not varchar.
September 26th, 2012, 08:34 AM
The two date fields you are doing comparisons on don't have indexes so that would slow you down for sure, the query would have to do a table scan for each comparison.