#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13

    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.

    Code:
    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)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT SUM(tx.amount) amount 
      FROM transactions tx
      LEFT
      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;
    or something like that.

    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    Unfortunately that query was slightly slower
    explain
    Code:
    '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
    Code:
    delimiter $$
    
    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',
      `description` text,
      `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$$
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Do many of your queries use MATCH/AGAINST or CONTAINS on account_id?
    Can account_id really be 45 characters in length?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    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.
  10. #6
  11. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    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.

IMN logo majestic logo threadwatch logo seochat tools logo