21

I'm using MySQL to store video game data. I have tables for titles, platforms, tags, badges, reviews, developers, publishers, etc...

When someone is viewing a game, is it best to have have one query that returns all the data associated with a game, or is it better to use several queries? Intuitively, since we have reviews, it seems pointless to include them in the same query since they'll need to be paginated. But there are other situations where I'm unsure if to break the query down or use two queries...

I'm a bit worried about performance since I'm now joining to games the following tables: developers, publishers, metatags, badges, titles, genres, subgenres, classifications... to grab game badges, (from games_badges; many-to-many to games table, and many to many to badges table) I can either do another join, or run a separate query.... and I'm unsure what is best....

Mohamad
  • 34,731
  • 32
  • 140
  • 219

3 Answers3

21

It is significantly faster to use one query than to use multiple queries because the startup of a query and calculation of the query plan itself is costly and running multiple queries in a row slows the server more each time. Obviously you should only get the data that you actually need, but fewer queries is always better.

So if you are going to show 20 games on a page, you can speed up the query (still using only one query) with a LIMIT clause and only run that query again later when they get to the next page. That or you can just make them wait for the query to complete and have all of the data there at once. One big wait or several little waits.

tl;dr use as few queries as possible.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • 21
    One should run a performance analysis before coming up with 'significantly faster'. –  Oct 11 '10 at 22:21
  • 1
    Yes you are right to say that "significantly faster" is a conclusion. This assumes that both query sets are giving the optimal amount of data. One query could be much slower if it is getting data you don't need as compared to many small queries that only get you exact data. – Explosion Pills Oct 11 '10 at 22:23
  • query performance depends not only on amount of data it returns. Even more: query performance depends on the query execution plan, which depends on the query conditions. – zerkms Oct 11 '10 at 22:38
  • 1
    Truth, but your query plan should have identical key types and you should only be getting the correct data each time anyway. Unless you need very specific conditions (and usually even then) you can do things with as simple of joins in one query than you could with multiple queries, but multiple queries will still cost more in that case. – Explosion Pills Oct 12 '10 at 01:23
20

There is no panacea.

  1. Always try to get only necessary data.
  2. There is no answer whether one big or several small queries is better. Each case is unique and to answer this question you should profile your application and examine queries' EXPLAINs
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • Just adding because beginners don't know this … you can latterly do `EXPLAIN select * from users ;` to get more information aboute the query … – Yasser CHENIK Sep 06 '22 at 13:03
10

This is generally a processing problem.

  • If making one query would imply retrieving thousands of entries, call several queries to have MySQL do the processing (sums, etc.).
  • If making multiple queries involves making tens or hundreds of them, then call a single query.

Obviously you're always facing both of these since neither is a goto option if you're asking the question, so the choices really are:

  1. Pick the one you can take the hit on
  2. Cache or mitigate it as much as you can so that you take a hit very rarely
  3. Try to insert preprocessed data in the database to help you process the current data
  4. Do the processing as part of a cron and have the application only retrieve the data
  5. Take a few steps back and explore other possible approaches that don't require the processing
reformed
  • 4,505
  • 11
  • 62
  • 88
srcspider
  • 10,977
  • 5
  • 40
  • 35