CODEWITHSUNDEEP
mysqlsqljoinself-join
user1660680
user1660680

Reputation: 97

Simplify Self Join SQL

I am currently doing the following join and it takes a very long time to execute the query, is there a way to make it run quicker? I have 23 fields in the table. I have a primary key on the field 'id' (int) 5 reg (varchar) 9

SELECT a1.*, ( SELECT COUNT(reg) FROM auction a2 WHERE reg = a1.reg) AS c1 FROM auction a1


CREATE TABLE IF NOT EXISTS `db1` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `adate` varchar(10) NOT NULL,
  `lnu` int(4) NOT NULL,
  `reg` varchar(9) NOT NULL,
  `mk` varchar(50) NOT NULL,
  `mod` varchar(50) NOT NULL,
  `type` varchar(25) NOT NULL,
  `ree` varchar(50) NOT NULL,
  `co` varchar(50) NOT NULL,
  `fu` varchar(20) NOT NULL,
  `tran` varchar(20) NOT NULL,
  `mt` varchar(9) NOT NULL,
  `mile` int(6) NOT NULL,
  `ree` int(6) NOT NULL,
  `apv` int(6) NOT NULL,
  `ds` int(2) NOT NULL,
  `sr` varchar(50) NOT NULL,
  `sen` int(10) NOT NULL,
  `keep` int(2) unsigned NOT NULL,
  `tmp` char(1) DEFAULT NULL,
  `rk` varchar(100) DEFAULT NULL,
  `st` varchar(3) DEFAULT NULL,
  `dd` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`adate`)

Upvotes: 1

Views: 254

Answers (4)

Suresh S
Suresh S

Reputation: 16

You have 2 options:

  1. Have an index on the join column i.e. reg . This may help fasten the query.

  2. Use CTE.

With CTE001 AS ( Select count() as GroupCount, reg from auction group by reg ) Select a., c.GroupCount from CTE001 c inner join auction a on c.reg = a.reg;

Upvotes: 0

John Woo
John Woo

Reputation: 263803

Seeing fromyour current schema, you should have provided index on reg column.

SELECT a1.*, a2.totalCount
FROM    auction a1
        INNER JOIN 
        (
            SELECT reg, COUNT(*)  totalCount
            FROM auction a2
            GROUP BY reg
        ) a2 
            ON a2.reg = a1.reg

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 51000

If, as stated, your only index is the primary key on (id, reg) then this query would be speeded up substantially by adding an index on (reg) by itself.

Upvotes: 2

Taryn
Taryn

Reputation: 247810

Have you tried using a sub-query:

SELECT a1.*, a2.cnt
FROM auction a1
INNER JOIN
(
    SELECT COUNT(reg) cnt, reg
    FROM auction
    GROUP BY reg
) a2
    on a1.reg = a2.reg

Upvotes: 1

Related Questions