reduce left join query excution time

Question

I'm developing an sql query that can join two table and it returns some results.
I have 2 tables in first table i save my order and in another table save my like information .
I want to show to user picture from order table that, user doesn't like picture yet . I use this query

SELECT amg_order.* 
  FROM amg_order 
  LEFT OUTER JOIN amg_like ON amg_like.order_id=amg_order.order_id
       AND amg_like.user_id=:user_id 
 WHERE amg_order.status = '1'
   AND amg_order.user_id != :user_id 
   AND (amg_like.user_id != :user_id || amg_like.user_id is null) 
 ORDER BY amg_order.likeType DESC, RAND()

This query return correct result but when like information be over 15000 time to execution this query has been 6 seconds .
Does anyone has any idea to reduce this time ?
I'm sorry my English is so bad :)

`ORDER BY .... RAND()` is killing your performance, probably. `RAND()` being the #1 culprit. — Andrei, Sep 04 '15 at 15:32
instead of giving * from just use column names and instead of this != use <> .if required use Order by other wise remove that faster retrieval of data ans use of RAND() ?? — mohan111, Sep 04 '15 at 15:34
Use EXPLAIN to analyze your statement: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html (you have to prepend EXPLAIN) — Naktibalda, Sep 04 '15 at 15:39
I remode order by RAND() and remove .* and just use column that i need but the time of excution is 5.5 yet — Armin, Sep 04 '15 at 15:50

Beroza Paul · Accepted Answer · 2015-09-04T16:32:59.457

1

You can try following query. This will of course reduce some of your execution time. You can specify fields name instead of * sign in your select statement.

Here is updated query:

SELECT amg_order.* FROM amg_order 
LEFT JOIN amg_like ON amg_order.order_id = amg_like.order_id 
WHERE amg_order.status= '1' AND amg_order.user_id != :user_id AND (amg_like.user_id != :user_id || amg_like.user_id is null) 
ORDER BY amg_order.likeType DESC LIMIT 10;

edited Sep 04 '15 at 16:32

answered Sep 04 '15 at 15:54

Beroza Paul

2,047
1
16
16

i added limit 1 at the end of the query but excution time is 5.5 – Armin Sep 04 '15 at 16:22
Did you try my version of query? The next step would be adding proper indexing to your tables amg_order and amg_like. – Beroza Paul Sep 04 '15 at 16:25
ALTER TABLE amg_order ADD INDEX (user_id); ALTER TABLE amg_like ADD INDEX (user_id); ALTER TABLE amg_like ADD INDEX (order_id); ALTER TABLE amg_order ADD INDEX (order_id); – Beroza Paul Sep 04 '15 at 16:26
You should add those indexes and try the query again. I think this will make you happy. :) – Beroza Paul Sep 04 '15 at 16:29
you remove some of code for example amg_order.user_id != :user_id this is need to dont show to user own order – Armin Sep 04 '15 at 16:30
run this query in phpmyadmin ? – Armin Sep 04 '15 at 16:32
Edited the query according to your need. If you don't have index on above mentioned fields, please add them before you run the query. Once you have those fields indexed, please run the above query. – Beroza Paul Sep 04 '15 at 16:35
thaaank you , you are beeeeeeeeeeeest – Armin Sep 04 '15 at 16:39
time excution 0.6 secend :D , but what happend when i add this index , the time is reduce over 6 secend – Armin Sep 04 '15 at 16:50
It's type of sorting table data for faster retrieve. You can read this up here: http://stackoverflow.com/questions/1108/how-does-database-indexing-work – Beroza Paul Sep 04 '15 at 17:01

reduce left join query excution time

1 Answers1