MySQL time out issue

Question

I am facing serious issue in my workout related to PHP and MySql on Linux server while when am running same code with same database in localhost, it's working fine.

As well as I have almost 30,000 records in database table and mysql is:

SELECT * FROM tbl_movies where id not in (select movie_id from tbl_usermovieque where user_id='3' union 
                                      select movie_id from tbl_user_movie_response where user_id='3' union 
                                      select movie_id from tbl_user_movie_fav where user_id='3') and id < 220 order by rand() limit 0,20

its taking 0.0010 sec in my localhost and INFINITE on our linux server. i unable to find the reason.

Thanks Kamal

you did tag the question with php, but show us only an SQL statement - did you try the statement on the SQL commandline or a DB Tool like phpmyadmin? maybe it's not the SQL statement that is slow but there is a problem in the communication between your php script and the sql server — cypherabe
– cypherabe, Commented Oct 15, 2015 at 15:14

ThinkTank · Accepted Answer · 2015-10-15 15:14:09Z

1

Can you confirm this return the same result ? It should be faster this way. Union are usefull sometime but not really optimized.

SELECT * FROM tbl_movies where id not in (
    select distinct movie_id
    from tbl_movies m
    inner join tbl_usermovieque um ON um.movie_id = m.movie_id = m.movie_id
    inner join tbl_user_movie_response umr ON umr.movie_id = m.movie_id = m.movie_id
    inner join tbl_user_movie_fav umf ON umf.movie_id = m.movie_id = m.movie_id
    where um.user_id = 3 or umr.user_id = 3 or umf.user_id = 3
) and id < 220 order by rand() limit 0,20;

PS : I assume you have Index un oser_id and id_movie

EDIT : your problem may come from rand()

MySQL order by optimization Look for RAND() in the page : in comment there are some performance test => rand() alone seams to be a bad solution

Performance

Now let's see what happends to our performance. We have 3 different queries for solving our problems.

Q1. ORDER BY RAND()

Q2. RAND() * MAX(ID)

Q3. RAND() * MAX(ID) + ORDER BY ID

Q1 is expected to cost N * log2(N), Q2 and Q3 are nearly constant.

The get real values we filled the table with N rows ( one thousand to one million) and executed each query 1000 times.

Rows ||100 ||1.000 ||10.000 ||100.000 ||1.000.000

Q1||0:00.718s||0:02.092s||0:18.684s||2:59.081s||58:20.000s Q2||0:00.519s||0:00.607s||0:00.614s||0:00.628s||0:00.637s Q3||0:00.570s||0:00.607s||0:00.614s|0:00.628s ||0:00.637s

As you can see the plain ORDER BY RAND() is already behind the optimized query at only 100 rows in the table.

edited Oct 15, 2015 at 15:14

answered Oct 15, 2015 at 15:03

ThinkTank

1,1919 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ThinkTank Over a year ago

Edited with link to order by optimisation

Hindu Over a year ago

Thanks, for your details answer but it is not in my case.

Collectives™ on Stack Overflow

MySQL time out issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related