Optimize MySQL Query (PHP)

Question

I use MySQL & PHP and have the following database requests:

$result1 = mysql_query("SELECT nummer FROM abo WHERE usr = '{$usr}'");

while($row = mysql_fetch_object($result1))
{
  $sendung=$row->sendung;
  $result2 = mysql_query("SELECT *
                            FROM filme 
                           WHERE sendung = '{$sendung}' 
                        ORDER BY datum DESC LIMIT 0,1;");

  while($row = mysql_fetch_object($result2))
  {
    [...]
  }
}

For example there the second while is called 20 times -> there are 20 database request. But could I connect this request or optimize this requests?

One approach:

$arraysendung = array();

$result1 = mysql_query("SELECT nummer FROM abo WHERE usr = '{$usr}'");

while($row = mysql_fetch_object($result1))
{
  array_push($arraysendung,$row->sendung);
}

But how could I use $arraysendung in one request (is it possible)?

[Start here](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Ron van der Heijden, Feb 15 '13 at 15:05

Marc B · Answer 1 · 2013-02-15T15:14:39.667

2

Use a JOIN

SELECT nummer, filme.*
FROM abo
JOIN filme ON filme.sendung = abo.nummer
ORDER BY datum desc

This isn't a 100% replacement, since the LIMIT stuff isn't in place, but should be enough to get you started.

For the most part, anytime you're running two+ queries, and the later queries are based on results from of earlier queries, you can almost always rewrite them into a single JOINed query.

edited Feb 15 '13 at 15:14

answered Feb 15 '13 at 15:06

Marc B

356,200
43
426
500

Actually, adding the "limit part" is not trivial. Also, you have a error in the JOIN condition it should read `filme.sendung = abo.nummer` – Sorin Feb 15 '13 at 15:13
Tpyo[sic] fixed. thanks. – Marc B Feb 15 '13 at 15:14

Sorin · Accepted Answer · 2013-02-15T16:52:34.620

1

The query:

SELECT filme.*
FROM filme JOIN 
    (SELECT sendung, max(datum) as datum FROM filme GROUP BY sendung) as md 
    ON filme.sendung = md.sendung AND md.datum = filme.datum
WHERE filme.sendung IN (SELECT nummer FROM abo WHERE  usr = '{$usr}')

should produce similar results, including the "LIMIT 1 part", provided "sendung,datum" is unique.

See: Subqueries, and JOIN

edited Feb 15 '13 at 16:52

answered Feb 15 '13 at 15:25

Sorin

5,201
2
18
45

thanks! why 'SELECT filme.*' and not 'SELECT *'? – user1756209 Feb 15 '13 at 16:17
`SELECT *` would include the fields from md alias, which are duplicates of the ones in filme, `SELECT filme.*` only includes the relevant fields. – Sorin Feb 15 '13 at 16:21
MySQL Error: "#1052 - Column 'sendung' in IN/ALL/ANY subquery is ambiguous" sendung is a varchar(255) in filme... what must I change? – user1756209 Feb 15 '13 at 16:30
edited the answer. `WHERE sendung IN` should be `WHERE filme.sendung IN` – Sorin Feb 15 '13 at 16:53

Optimize MySQL Query (PHP)

2 Answers2