MySQL Forums
Forum List  »  Optimizer & Parser

Need recommendation regarding IN expression
Posted by: Solon Barns
Date: February 22, 2006 12:52PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Need recommendation regarding IN expression
3176
February 22, 2006 12:52PM


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.