Actually I am trying to run my query on the netflix dataset that have been provided. I loaded the data and want to run the query on that database. Its in mysql.
And this is what i wrote
select a.movie_id,a.customerid,a.rating,a.rating_date,b.movie_id,b. customerid,b.rating,b.rating_date from (select * from ratings where customerid IN (select customerid from ratings where movie_id =1)) as a, (select * from ratings where customerid = 1046323) as b where a.movie_id = b.movie_id;
This single query has already been running for like 2 days and i still haven’t got the output. Can someone tell me how to make this query effective.
I have a table called ratings which contains almost 100480394 tuples and the schema is like
id(int) | movie_id(int) | customerid(int) | rating(smallint) | date
Indexed on movie_id and customer_id.
I want to make it work real fast because i have to run this query like 2000000 times for each tuple on the qualifying list which has almost 2000000 tuples.
when i
(select * from ratings where customerid IN (select customerid from ratings where movie_id =1)) this query alone takes a too long time i think almost a day or 2. and rest of the queries are fine i guess.
Can sombody please help.
Here is the procedure i am trying to execute
delimiter ‘/’;
DROP PROCEDURE IF EXISTS getdata;
create procedure getdata()
begin
declare movie INT;
declare customer INT;
declare ind1 int;
declare cur_1 cursor for select movie_id,customer_id from qualifying;
declare continue handler for sqlstate ‘02000’ set ind1 = (select count(*) from qualifying);
open cur_1;
repeat
FETCH cur_1 INTO movie,customer;
set @myvar = concat(‘select a.movie_id,a.customerid,a.rating,a.rating_date,b.movie_id,b. customerid,b.rating,b.rating_date into outfile ‘,"’" ,’/home/symbolic/Desktop/netflix/output/netflix_‘,movie,’_‘, customer, "’“,’ fields terminated by ',”‘",’\t’,“'”,’ lines terminated by ‘,"’“,‘\n’,”‘",’ from (select * from ratings where customerid IN (select customerid from ratings where movie_id =‘, movie,’)) as a, (select * from ratings where customerid =‘, customer,’) as b where a.movie_id = b.movie_id’);
prepare stmt1 from @myvar;
Execute stmt1;
Deallocate prepare stmt1;
until ind1 end repeat;
close cur_1;
end;/
Bishwash Raj Giri