Database speed optimisation (multiple queries)

Question

I have to develop a web application for movies. For now, in my database, I have a table for the relations between figures and movies and another for the relations between actors (wich are also figures) and movies. The last one also contains information about the character.

My question in not about the normalization at all, I would like to know if it's more efficient in term of speed to do two queries for each tables or to merge them to a single one with NULL values for character in some tuples and do a single query ?

EDIT:

The two queries for now (figure side, to get his acting and career):

$query1 = '
        SELECT RelationFigureCardType.department, RelationFigureCardType.job, Movie.card_type_id, Movie.id, Movie.original_name, Movie.release_date, Movie.cover, Movie.cover_ext
        FROM relation_figure_cards AS RelationFigureCard
            RIGHT JOIN relation_figure_card_types AS RelationFigureCardType ON 
                (RelationFigureCard.relation_figure_card_type_id = RelationFigureCardType.id)
            RIGHT JOIN cards AS Movie ON
                (RelationFigureCard.card_id = Movie.id)
        WHERE 
            RelationFigureCard.figure_card_id = '.$figureId.'
            AND RelationFigureCard.last_card_version = 1
        ORDER BY Movie.card_type_id, RelationFigureCardType.priority, Movie.release_date
        ;
    ';

$query2 = '
        SELECT RelationActorCard.is_main, RelationActorCard.is_guest, Movie.card_type_id, Movie.id, Movie.original_name, Movie.release_date, Movie.cover, Movie.cover_ext, Character.id, Character.original_name, Character.cover, Character.cover_ext
        FROM relation_actor_cards AS RelationActorCard
            RIGHT JOIN cards AS `Character` ON 
                (RelationActorCard.character_card_id = Character.id)
            RIGHT JOIN cards AS Movie ON
                (RelationActorCard.card_id = Movie.id)
        WHERE 
            RelationActorCard.figure_card_id = '.$figureId.'
            AND RelationActorCard.last_card_version = 1
        ORDER BY Movie.card_type_id, Movie.release_date
        ;
    ';

Lumi · Accepted Answer · 2011-07-16 09:31:09Z

1

I'd strongly suspect the single-query approach to be faster.

I'd even more strongly suspect it to be faster when you're not using a stored procedure but executing the two queries one after the other from a scripting interpreter like PHP, or any other process that allows you to interface with MySQL.

If you want a reliable answer for your case, there is a very simple answer:

Perform a test for your particular scenario with real data and compare the performance. (This is called "benchmarking".)

answered Jul 16, 2011 at 9:31

Lumi

15.5k9 gold badges64 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sébastien Over a year ago

I suspect a little sarcasm in your last sentence :), but you're right. It's just that I don't have enough data for now to do this.

Lumi Over a year ago

@Sébastien - Well, I remember there was a time when I didn't know what benchmarking was, so why not explain it? No sarcasm intended here ...

Maxim Krizhanovsky · Accepted Answer · 2011-07-16 09:27:06Z

1

Depends on the query. LEFT JOIN is something normal in MySQL. Make a single query with proper indexes. Do not optimize it (break to smaller queries), unless it's necessary

answered Jul 16, 2011 at 9:27

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

2 Comments

Sébastien Over a year ago

I do not have to do a JOIN, it's just a simple SELECT of the tuples that have the required movie ID. There is two possibilities: two time this query on the two tables (with less data in each of them) or a single time in a big table containing all the data.

Maxim Krizhanovsky Over a year ago

Or maybe you can use UNION of the 2 queries? Anyway, without the exact queries, it's hard to answer

Collectives™ on Stack Overflow

Database speed optimisation (multiple queries)

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related