mysql select query optimization
- by Saharsh Shah
I have two table testa & testb.
CREATE TABLE `testa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `testb` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
`aid1` INT(10) DEFAULT NULL,
`aid2` INT(10) DEFAULT NULL,
`aid3` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`)
);
Currently I am running below query for retrieving all rows where id in testa table matches with any columns of aid1,aid2,aid3 in tableb. The query is retreiving acurate result but it is taking minimum 30 seconds to execute which is too much. I have also tried to optimise my query using UNION but failed to do so.
SELECT a.id, a.name, b.name, b.id
FROM testb b
INNER JOIN testa a ON b.aid1 = a.id OR b.aid2 = a.id OR b.aid3 = a.id ;
How do i optimize my query so it's total execution time is within 2-3 seconds?
Thanks in advance...
