Announcement

Announcement Module
Collapse
No announcement yet.

Having problem with queries

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Having problem with queries

    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.m ovie_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.m ovie_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
Working...
X