Databases
StackOverflow2013
Postsdbo
POMYSQL query optimization, multiple queries or one large query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMYSQL query optimization, multiple queries or one large query
    primarykey
    data
    text
    <p>I have a query which has some sub queries (inner selects), i'm trying work out which is better for performance, one larger query or lots of smaller queries, i'm finding it difficult to try and time the differences as it changes all the time on my server.</p> <p>I use the query below to return 10 results at a time to display on my website, using pagination (offset and limit).</p> <pre><code>SELECT adverts.*, breed.breed, breed.type, sellers.profile_name, sellers.logo, users.user_level , round( sqrt( ( ( (adverts.latitude - '51.558430') * (adverts.latitude - '51.558430') ) * 69.1 * 69.1 ) + ( (adverts.longitude - '-0.0069345') * (adverts.longitude - '-0.0069345') * 53 * 53 ) ), 1 ) as distance, ( SELECT advert_images.image_name FROM advert_images WHERE advert_images.advert_id = adverts.advert_id AND advert_images.main = 1 LIMIT 1) as imagename, ( SELECT count(advert_images.advert_id) from advert_images WHERE advert_images.advert_id = adverts.advert_id ) AS num_photos FROM adverts 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) 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 &lt;= '20') ORDER BY distance ASC LIMIT 0,10 </code></pre> <p>Would it be better to remove the 2 inner selects below from the main query, and then in my php loop, call the 2 selects 10 times, once for each record in the loop?</p> <pre><code>( SELECT advert_images.image_name FROM advert_images WHERE advert_images.advert_id = adverts.advert_id AND advert_images.main = 1 LIMIT 1) as imagename, ( SELECT count(advert_images.advert_id) from advert_images WHERE advert_images.advert_id = adverts.advert_id ) AS num_photos </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser1052096
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMYSQL query optimization, multiple queries or one large query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMYSQL query optimization, multiple queries or one large query
      1. USJSK NS
      1. VTFavorite
    3. VO
      singulars
      1. POMYSQL query optimization, multiple queries or one large query
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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