[soyouthink]

ok. basicly: when you run this query over php, mysql parse query each time. but in procedure it cache the query. this point is just an extra optimization its not about what you ask.

Optimization for what you ask is this:

CREATE PROCEDURE `SP_MOVIE_GET`(
    IN t_id VARCHAR(20)
)
BEGIN
    IF(SUBSTRING(t_id, 1, 2 ) = 'tt')THEN
        SELECT 
            *,
            IFNULL(SUM(likes.vote), 0) AS likecount
        FROM 
            movie_images img 
            LEFT JOIN movie_items item ON item.movie_tmdb_id = img.image_movie_tmdb_id
            LEFT JOIN fanart_types ON type_id = image_type 
            LEFT JOIN fanart_image_like likes ON likes.like_image_id = img.image_id and likes.type = '3'
        WHERE 
            item.movie_imdb_id = t_id
            AND img.image_active = 'y'
        GROUP BY img.image_id 
        ORDER BY likecount DESC;
    ELSE
        SELECT 
            *,
            IFNULL(SUM(likes.vote), 0) AS likecount
        FROM 
            movie_images img 
            LEFT JOIN movie_items item ON item.movie_tmdb_id = img.image_movie_tmdb_id
            LEFT JOIN fanart_types ON type_id = image_type 
            LEFT JOIN fanart_image_like likes ON likes.like_image_id = img.image_id and likes.type = '3'
        WHERE 
            img.image_movie_tmdb_id = t_id
            AND img.image_active = 'y'
        GROUP BY img.image_id 
        ORDER BY likecount DESC;
    END IF;
END

i wrote this on fly. didnt test so it may have some simple comma mistakes etc.

[Kode]

no, themoviedb org ids are all numeric, hence being stored in an int, the tt[0-9] are imdb ids and are in a varchar, the lookup on the tmdb int only is not much different if any to the imdb varchar lookup.

Never tried a stored procedure, what difference would a stored procedure make?

[soyouthink]

1- "tt[0-9]+" is a valid format for all themoviedb.org ids?
2- is this query run in a stored procedure?

[Kode]

a movie entry (say thor) stores both the tmdb id (http://www.themoviedb.org/ in the case of thor 10195) and the imdb id (tt0800369), either of which can be searched on in the API (depending on what the third party uses for matching against)

so for example:

http://api.fanart.tv/webservice/movi.../json/all/1/2/
or
http://api.fanart.tv/webservice/movi.../json/all/1/2/

[soyouthink]

What is the relation between movie_items.movie_imdb_id and movie_items.movie_tmdb_id?

[Kode]

its the third one down, keyname is show for some reason, but the column is right, I also removed the index and re-added it just to check the name wasn't the issue (didn't think it was, but just to rule it out), no difference.

Using explain I found out that the items part wasn't being indexed properly, using http://www.techfounder.net/2008/10/1...ions-in-mysql/ I also realised what while the tmdb is an int the imdb id is a string and the lookup didn't have quotes around it, while this didn't make much difference to my original query, doing a union and adding the quotes to the query below made a massive difference!

(SELECT *, IFNULL(SUM(likes.vote), 0) AS likecount FROM movie_images img LEFT JOIN movie_items item ON item.movie_tmdb_id = img.image_movie_tmdb_id LEFT JOIN fanart_types ON type_id = image_type LEFT JOIN fanart_image_like likes ON likes.like_image_id = img.image_id and likes.type = '3' WHERE img.image_movie_tmdb_id = 10195 AND img.image_active = 'y' GROUP BY img.image_id) UNION ALL (
SELECT *, IFNULL(SUM(likes.vote), 0) AS likecount FROM movie_images img LEFT JOIN movie_items item ON item.movie_tmdb_id = img.image_movie_tmdb_id LEFT JOIN fanart_types ON type_id = image_type LEFT JOIN fanart_image_like likes ON likes.like_image_id = img.image_id and likes.type = '3' WHERE item.movie_imdb_id = '10195' AND img.image_active = 'y' GROUP BY img.image_id
)
ORDER BY likecount DESC

comes out at around 0.0188

I'm still trying to get it to work on the last example using:

SELECT *,
IFNULL(SUM(likes.vote), 0) AS likecount
FROM movie_images img
LEFT JOIN movie_items item ON item.movie_tmdb_id = img.image_movie_tmdb_id
LEFT JOIN fanart_types ON type_id = image_type LEFT JOIN fanart_image_like likes ON likes.like_image_id = img.image_id and likes.type = '3'
WHERE (img.image_movie_tmdb_id = 10195 OR item.movie_imdb_id = '10195') AND img.image_id IN (SELECT image_id FROM movie_images WHERE img.image_active = 'y') GROUP BY img.image_id ORDER BY likecount DESC

but explain is saying it's still not using the index

[elcct]

The biggest thing slowing the query down seems to be the OR statement WHERE (img.image_movie_tmdb_id = 10195 OR item.movie_imdb_id = 10195)
doing either of them on their own the query is 0.0147 with the OR the query is around 0.2495, any suggestions for optimising the query to reduce this?

Looks like you don't have index for image_movie_tmdb_id in movie_images

[Kode]

The biggest thing slowing the query down seems to be the OR statement WHERE (img.image_movie_tmdb_id = 10195 OR item.movie_imdb_id = 10195)
doing either of them on their own the query is 0.0147 with the OR the query is around 0.2495, any suggestions for optimising the query to reduce this?

[Kode]

Does anyone have any suggestions for optimising the following statement?

SELECT *, IFNULL(SUM(likes.vote), 0) AS likecount FROM movie_images img LEFT JOIN movie_items item ON item.movie_tmdb_id = img.image_movie_tmdb_id LEFT JOIN fanart_types ON type_id = image_type LEFT JOIN fanart_image_like likes ON likes.like_image_id = img.image_id and likes.type = '3' WHERE (img.image_movie_tmdb_id = 10195 OR item.movie_imdb_id = 10195) AND img.image_active = 'y' GROUP BY img.image_id ORDER BY likecount DESC

movie_items indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No movie_id 11607 A
movie_imdb_id BTREE No No movie_imdb_id 11607 A
movie_tmdb_id BTREE No No movie_tmdb_id 11607 A

movie_images indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No image_id 50238 A
image_active BTREE No No image_active 2 A
image_show_thetvdb_id BTREE No No image_movie_tmdb_id 25119 A
image_type BTREE No No image_type 14 A
image_replace BTREE No No image_replace 2 A
image_date BTREE No No image_date 50238 A

fanart_types indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No type_id 40 A
type_var BTREE No No type_var 40 A
type_section BTREE No No type_section 8 A

fanart_image_like indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
user_id BTREE Yes No user_id 3382 A
------------------------ like_image_id 47349 A
type BTREE No No type 2 A
like_image_id BTREE No No like_image_id 47349 A