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.
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