Need recommendation regarding IN expression
Suppose I have these tables:
CREATE TABLE `registrant`(
`registrant_id` INT AUTO_INCREMENT,
`fname` VARCHAR(50) NOT NULL,
PRIMARY KEY (`registrant_id`),
)TYPE=MYISAM;
CREATE TABLE `page_view`(
`registrant_id` INT NOT NULL,,
`time_on_page` INT NOT NULL,
INDEX `Ref169263`(`registrant_id`),
)TYPE=MYISAM;
CREATE TABLE `selected_registrants`(
`report_id` INT NOT NULL,
`registrat_id` INT NOT NULL,
)TYPE=MYISAM;
registrant table is about 100K rows, and page_view table is several million rows.
I need to do aggregate reports but only for selected registrants. The selection criteria is so complex that I decided to fetch the registrant IDs seperately and then use a list of IDs in a subquery:
Will the following query be efficient?
SELECT SUM(time_on_page) FROM page_view WHERE registrant_id IN (SELECT registrant_id FROM selected_registrants WHERE report_id = 1569) GROUP BY registrant_id
My concern is that number of IDs in the IN expression can be about 100K. Is there a better way to do this?
Thanks, Solon.
Subject
Views
Written By
Posted
Need recommendation regarding IN expression
3258
February 22, 2006 12:52PM
2191
February 22, 2006 01:29PM
1925
February 23, 2006 12:59AM
2205
February 23, 2006 10:55AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.