StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h1>Avoid subqueries</h1> <p>As I understand your inner selects, they serve two purposes: find <em>any</em> name of an associated image, and <em>count</em> the number of associated images. You probably might achieve both using a left join instead of an inner select:</p> <pre><code>SELECT …, advert_images.image_name AS imagename, COUNT(advert_images.advert_id) AS num_photos, … FROM … LEFT JOIN advert_images ON advert_images.advert_id = adverts.advert_id … GROUP BY adverts.advert_id … LIMIT 0,10 </code></pre> <p>I haven't tried this, but perhaps the MySQL engine is smart enough to only perform that part of the query for the rows you're actually returning.</p> <p>Note that there are no guarantees at all about <em>which</em> image name this query will return for a given set of images. If you want reproducible results, you should use some aggregating function there, e.g. <code>MIN(advert_images.image_name)</code> to select the lexicographically first image.</p> <h1>Separate select but no loop</h1> <p>If the above does not work, i.e. the query will still examine the <code>advert_images</code> table for <em>all</em> rows of the computed result, then you're probably really better off by performing a second query. You might however try avoiding the <code>for</code> loop, and instead fetch all those rows in a single query:</p> <pre><code>SELECT advert_images.image_name AS imagename, COUNT(advert_images.advert_id) AS num_photos FROM advert_images WHERE advert_images.advert_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) GROUP BY advert_images.advert_id </code></pre> <p>The ten parameters in this query correspond to the ten rows of the result you're currently generating. Note that an advert <em>without</em> an associated photo will not be included in that result at all. So make sure to default <code>num_photos</code> to zero and <code>imagename</code> to <code>NULL</code> in your code.</p> <h1>Temporary table</h1> <p>Another way to achieve what you're attempting to do would be to use an explicit temporary in-memory table: first you select the results you're interested in, and then you retrieve all the associated information.</p> <pre><code>CREATE TEMPORARY TABLE tmp SELECT adverts.advert_id, round(…) as distance FROM adverts WHERE (adverts.status = 1) AND (adverts.approved = 1) AND (adverts.latitude BETWEEN 51.2692837281 AND 51.8475762719) AND (adverts.longitude BETWEEN -0.472015213613 AND 0.458146213613) HAVING (distance <= 20) ORDER BY distance ASC LIMIT 0,10; SELECT tmp.distance, adverts.*, … advert_images.image_name AS imagename, COUNT(advert_images.advert_id) AS num_photos, … FROM tmp INNER JOIN adverts ON tmp.advert_id = adverts.advert_id LEFT JOIN breed ON adverts.breed_id = breed.breed_id LEFT JOIN sellers ON adverts.user_id = sellers.user_id LEFT JOIN users ON adverts.user_id = users.user_id LEFT JOIN advert_images ON advert_images.advert_id = adverts.advert_id GROUP BY adverts.advert_id ORDER BY tmp.distance ASC; DROP TABLE tmp; </code></pre> <p>This will ensure that all the other tables are only queried for the results you're currently working on. After all, there is little magic about the <code>advert_images</code> table except the fact that you might want multiple rows from it.</p> <h1>Subquery as join factor</h1> <p>Building on the approach from the preceding paragraph, you can even avoid managing a temporary table, and use a subquery in its stead:</p> <pre><code>SELECT sub.distance, adverts.*, … advert_images.image_name AS imagename, COUNT(advert_images.advert_id) AS num_photos, … FROM ( SELECT adverts.advert_id, round(…) as distance FROM adverts WHERE (adverts.status = 1) AND (adverts.approved = 1) AND (adverts.latitude BETWEEN 51.2692837281 AND 51.8475762719) AND (adverts.longitude BETWEEN -0.472015213613 AND 0.458146213613) HAVING (distance <= 20) ORDER BY distance ASC LIMIT 0,10; ) AS sub INNER JOIN adverts ON sub.advert_id = adverts.advert_id LEFT JOIN breed ON adverts.breed_id = breed.breed_id LEFT JOIN sellers ON (adverts.user_id = sellers.user_id) LEFT JOIN users ON (adverts.user_id = users.user_id) LEFT JOIN advert_images ON advert_images.advert_id = adverts.advert_id GROUP BY adverts.advert_id ORDER BY sub.distance ASC </code></pre> <p>Again you determine the relevant rows by using data from the <code>adverts</code> table only, and join only the required rows from other tables. Most likely, that intermediate result will internally be stored in a temporary table, but that is up to the SQL server to decide.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload