Optimize SQL query inside foreach

Question

I need help optimizing the below queries for a recurrent calendar I've built.

If user fails to accomplish all task where date < today, I want to show a reminder which states the date and title of the task. Since the calendar uses recurrent events the same ID can be targeted multiple times.

This is the query I use inside a foreach which fetches all dates that the current activity is active.

This is my current setup, which works, but is very slow.

Other string explained:

$today=date("Y-m-d");
$parts = explode($separator, $datespan);
$dayForDate2 = date("l", mktime(0, 0, 0, $parts[1], $parts[2], $parts[0]));
$week2 = strtotime($datespan); 
$week2 = date("W", $week2);
if($week2&1) { $weektype2 = "3"; } # Odd week 1, 3, 5 ... 
else         { $weektype2 = "2"; } # Even week 2, 4, 6 ...

Query #1:

$query1 = "SELECT date_from, date_to, bok_id, kommentar
           FROM bokningar
           WHERE bokningar.typ='2'
             AND date_from < '".$today."'";

Function that makes the foreach move ahead one day at the time...

function date_range($first, $last, $step = '+1 day', $output_format = 'Y-m-d' )
{
  $dates = array();
  $current = strtotime($first);
  $last = strtotime($last);

  while( $current <= $last ) {

    $dates[] = date($output_format, $current);
    $current = strtotime($step, $current);
  }

  return $dates;
}

foreach:

foreach (date_range($row['date_from'], $row['date_to'], "+1 day", "Y-m-d")
         as $datespan)
   if ($datespan < $today)

Query #2:

$query2 = "
    SELECT bok_id, kommentar
        FROM bokningar b
        WHERE b.typ='2'
          AND b.bok_id='".$row['bok_id']."'
          AND b.weektype = '1'
          AND b.".$dayForDate2." = '1'
          AND NOT EXISTS 
              (SELECT t.tilldelad, t.bok_id
                  FROM tilldelade t
                  WHERE t.tilldelad = '".$datespan."'
                    AND t.bok_id='".$row['bok_id']."')
    
           OR   b.typ='2'
          AND b.bok_id='".$row['bok_id']."'
          AND b.weektype = '".$weektype2."'
          AND b.".$dayForDate2." = '1'
          AND NOT EXISTS 
                   (SELECT t.tilldelad, t.bok_id
                       FROM tilldelade t
                       WHERE t.tilldelad = '".$datespan."'
                         AND t.bok_id='".$row['bok_id']."')";

b.weektype is either 1,2 or 3 (every week, every even week, every uneven week)

Rick James · Accepted Answer · 2017-01-31 05:24:14Z

1

bokningar needs INDEX(typ, date_from)

Instead of computing $today, you can do

and date_from < CURDATE()

Are you running $query2 for each date? How many days is that? You may be able to build a table of dates, then JOIN it to bokningar to do all the SELECTs in a single SELECT.

When doing x AND y OR x AND z, first add parenthes to make it clear which comes first AND or OR: (x AND y) OR (x AND z). Then use a simple rule in Boolean arithmetic to transform it into a more efficient expression: x AND (y OR z) (where the parens are necessary).

The usual pattern for EXISTS is EXISTS ( SELECT 1 FROM ... ); there is no need to list columns.

If I am reading it correctly, the only difference is in testing b.weektype. So the WHERE can be simply

WHERE b.weektype IN ('".$weektype2."', '1')
  AND ...

There is no need for OR, since it is effectively in IN().

tilldelade needs INDEX(tilldelad, bok_id), in either order. This should make the EXISTS(...) run faster.

Finally, bokningar needs INDEX(typ, bok_id, weektype) in any order.

That is a lot to change and test. See if you can get those things done. If it still does not run fast enough, start a new Question with the new code. Please include SHOW CREATE TABLE for both tables.

answered Jan 31, 2017 at 5:24

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

2 Comments

Martin Over a year ago

Hi and thanks for the reply! I've manage to make the query faster - im still not satisfied but the query time dropped from avg 2.1 sec to 0.3 which works fine for now!

Rick James Over a year ago

Perhaps you want to start a new Question with your current query? I provided only what I could see from the surface; there may be more that can be done. Be sure to include SHOW CREATE TABLE, too.

Collectives™ on Stack Overflow

Optimize SQL query inside foreach

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