database - Mysql ONLY_FULL_GROUP_BY mode issue - Pick single Image URL(any) for each Album

Question

I need help in a mysql query, here are the details:

Three Tables, Album_Master , Album_Photo_Map, Photo_Details

Album_Master Table Structure

album_id(P) | album_name | user_id(F)
1               abc           1
2               xyz           1
3               pqr           1
4               e3e           2

Album_Photo_Map Table Structure

auto_id(P) | album_id(F) | photo_id
1             1              123
2             1              124
3             2              123 
4             2              125
5             1              127
6             3              127

Photo_Details Table Structure

auto_id(P) | image_id(F) | image_url
1               123          http....
2               124          http....
3               125          http...

I want to write a query to get the album name with image url for user_id 1 The output I am expecting here is

album_id | album_name | image_url
1            abc          http.. (either 123 or 124 or 127 - only one url)
2            xyz          http.. (either 123 or 125 - only one)
3            pqr          http.. (127 only)

The query I am using is taking too much time to execute, almost 8s.

SELECT A.album_id
     , A.album_name
     , D.image_url 
  from Album_Master A
     , Album_Photo_Map E 
  LEFT 
  JOIN Photo_Details D 
    ON (D.image_id = (
      SELECT P.photo_id 
        FROM Album_Photo_Map P
           , Photo_Details Q 
       WHERE P.photo_id = Q.image_id 
         AND P.album_id = E.album_id limit 0,1)
       ) 
 WHERE A.album_id = E.album_id 
   AND A.user_id = 1 
 group 
    by A.album_id
     , E.album_id
     , D.image_url;

I am looking for an optimize version of the query, any help will be really appreciated. If I use image_url in group by it is creating multiple records, also if I remove D.image_url it gives me error

D.image_url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Note: user can assign one photo in multiple albums, result should pick only one photo per album, there might be 100 of photos in an album.

Answer 1

If it doesn't matter which url it should include in resultset then you can just do group by album_id only and you are good to go.

SELECT 
    A.album_id, A.album_name, D.image_url 
FROM album_master A
INNER JOIN album_photo_map P ON A.album_id = P.album_id
INNER JOIN photo_details D ON P.photo_id = D.image_id
GROUP BY A.album_id;

Note: If you want album info even there is no photo attached to it then use LEFT JOIN instead of INNER JOIN in query.

Functional Dependency Issue due to ONLY_FULL_GROUP_BY

MySQL 5.7.5 and later implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them

Specific Issue: D.image_url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Solution 1: If possible use aggregate function for the other columns which are not included in group by clause.

Solution 2: If you know that, for a given data set, each album_id value in fact uniquely determines the image_url value that means image_url is effectively functionally dependent on album_id. So you can do

SELECT 
    A.album_id, A.album_name, ANY_VALUE(D.image_url) AS image_url 
FROM album_master A
INNER JOIN album_photo_map P ON A.album_id = P.album_id
INNER JOIN photo_details D ON P.photo_id = D.image_id
GROUP BY A.album_id;

Solution 3: Alternatively Disable only_full_group_by in mysql

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

A more info check Solve Query Failures Regarding ONLY_FULL_GROUP_BY SQL MODE