How to optimize Mysql query with for loop

Question

This query is taking long time to execute. Anybody tell me how to optimize this query?

$sql = '';
        $now = time();
        $until = $now + 604800;

        for ($i=0; $i < 7 ; $i++) { 
            $dayno = date('N', $now + ($i * 86400));
            $sql  .= "SELECT p.id, p.iam, p.offertimes, p.fromcity, p.tocity, p.days, p.parcel, p.seats, p.price, 
                        CASE 
                            WHEN days LIKE '%{$dayno}%' THEN 
                            (select unix_timestamp(addtime(timestamp(date(now())+{$i}),time(from_unixtime(deadline)))))
                            ELSE deadline
                        END AS deadline,

                            c1.nameRu AS 'from', 
                            c2.nameRu AS 'to', 
                            c3.nameRu AS 'city' 
                        FROM posts AS p 
                        LEFT JOIN citynames AS c1 ON p.from_id  = c1.id
                        LEFT JOIN citynames AS c2 ON p.to_id    = c2.id
                        LEFT JOIN citynames AS c3 ON p.city_id  = c3.id
                        WHERE p.deadline > {$now} ";
                        if ($i == 0) {
                            $sql .= "AND {$now} < (SELECT unix_timestamp(addtime(timestamp(date(now())), time(from_unixtime(deadline))))) "; 
                        }
                        $sql .= "AND p.days LIKE '%{$dayno}%' UNION ALL ";
        }
        $sql  .= "SELECT p.id, p.iam, p.offertimes, p.fromcity, p.tocity, p.days, p.parcel, p.seats, p.price, p.deadline, 
                        c1.nameRu AS `from`, 
                        c2.nameRu AS `to`, 
                        c3.nameRu AS `city` 
                    FROM posts AS p 
                    LEFT JOIN citynames AS c1 ON p.from_id  = c1.id
                    LEFT JOIN citynames AS c2 ON p.to_id    = c2.id
                    LEFT JOIN citynames AS c3 ON p.city_id  = c3.id
                    WHERE p.deadline > {$now} AND p.days = '' ORDER BY `deadline` ASC";

UPDATED: I have to 2 types of posts 1st is once and departure (e.g 25 march 2015, 7:00 AM), 2nd is regular and departure (e.g. every Monday, Friday, Sunday at 8:AM until 12 April 2015). What i want is: when user search code must convert all regular posts to 1st post type according to name of week day (until next week) but i must keep hours and minutes as old. I save my departure in one field (deadline) for regular posts additional filed (days). Here is my mysql table (short):

offertime|from_id|to_id|via_id|deadline  |days
2        |     52|   43|    73|1432036859|1,2,7
1        |     72|   65|    66|1424860459|
2        |     55|   23|    88|1425149999|1,2,3,4,5,6,7

offertime = 2 (regular)
offertime = 1 (once)

why do you join 3 times the same table???show exanple of raw data and expected result of query? — Alex
– Alex, Commented Feb 21, 2015 at 17:26
Oops -- 3 joins are needed because 3 different rows are needed. — Rick James
– Rick James, Commented Feb 21, 2015 at 18:57

Rick James · Accepted Answer · 2015-02-21 19:16:23Z

2

Some suggested improvements, plus lots of questions:

posts needs "compound" INDEX(days, deadline). Please show us SHOW CREATE TABLE so we can see the datatypes and indexes.

Can you avoid the leading wildcard in LIKE '%{$dayno}%'? Please show us sample values for these.

unix_timestamp(addtime(timestamp(date(now())+{$i}) --> UNIX_TIMESTAMP(CURDATE() + INTERVAL {$i} DAY

Please explain what this is supposed to do. It is not doing that! (select unix_timestamp(addtime(timestamp(date(now())+{$i}), time(from_unixtime(deadline))))) I say that because time(from_unixtime('2014-01-01')) = 16:33:34.000000, which is probably not what you expected.

{$now} --> NOW()

$until is not used?

Would it make sense to JOIN to citynames after the UNION?

SELECT ...
    FROM ( your union without citynames )
    LEFT JOIN citynames <3 times>

You have a complex expression with deadline buried inside. Try to turn it inside out into

AND deadline > (some complex constant expression)

That way, an index might be usable. (And hence speed up the query.)

Please "echo" the resulting SQL and let us critique that; it is hard to work from PHP.

Please put in the suggestions, get the echo, then let's go at it again.

answered Feb 21, 2015 at 19:16

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

3 Comments

Newmaster Over a year ago

Rick James I have tried add citynames after UNION but i could not :( i am new to MySQL. I have updated my question

Rick James Over a year ago

Please echo the SQL after you have made whatever changes you can make. I would much prefer to work with SQL without the PHP stuff interspersed.

Newmaster Over a year ago

SELECT p.id, p.iam, p.offertimes, p.fromcity, p.tocity, p.days, p.parcel, p.seats, p.price, CASE WHEN days != '' THEN (select unix_timestamp(addtime(timestamp(date(now())+6),time(from_unixtime(deadline))))) ELSE deadline END AS deadline FROM posts AS p WHERE p.deadline > 1424590473 AND p.days LIKE '%6%' UNION ALL LEFT JOIN citynames AS c1 ON p.from_id = c1.id LEFT JOIN citynames AS c2 ON p.to_id = c2.id LEFT JOIN citynames AS c3 ON p.city_id = c3.id

O. Jones · Accepted Answer · 2015-02-21 17:39:24Z

I suspect this line is causing your performance problems:

AND {$now} < (SELECT unix_timestamp(
                         addtime(
                             timestamp(date(now())), 
                             time(from_unixtime(deadline)))))

It looks to me like you have a deadline each day for each row. The daily deadline seems to be TIME(deadline).

Can you try to simplify this line, especially to get rid of the nested SELECT?

Maybe this will do the trick.

AND TIME(deadline) <= TIME(NOW())

Also, you seem to be repeating this query once for each of the previous days. It's possible you could run the query just once and group by day. But I don't understand your application well enough to try to do write that query.

Rick James · Accepted Answer · 2015-02-22 07:56:10Z

His query, pretty-printed and fixed some:

SELECT  p.id, p.iam, p.offertimes, p.fromcity, p.tocity, p.days,
        p.parcel, p.seats, p.price,
        CASE WHEN days != '' THEN 
            ( SELECT  unix_timestamp(addtime(timestamp(date(now())+6),
                time(from_unixtime(deadline)))))
            ELSE deadline
            END AS deadline
    FROM  posts AS p
    LEFT JOIN  citynames AS c1 ON p.from_id = c1.id
    LEFT JOIN  citynames AS c2 ON p.to_id = c2.id
    LEFT JOIN  citynames AS c3 ON p.city_id = c3.id 
    WHERE  p.deadline > 1424590473
      AND  p.days LIKE '%6%'

Can '%6%' be turned into simply 6 or at least '6'? If so, we might have some more index opportunities. Such as INDEX(days, deadline).

We have already discussed how ADDTIME is wrong; please fix that.

Notice that I got rid of the UNION (not necessary in this shortened version) and moved the WHERE to the end.

Didn't your original query have a UNION?

Collectives™ on Stack Overflow

How to optimize Mysql query with for loop

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related