How can I improve my query so it takes less time to execute, and I can still divide the result into pages using multiple tables

I am trying to create a PHP website that would allow users to see a leaderboard of online game players by their ranking.

The ranking consists of following components:

• Rank (Challenger, Grandmaster, Master, Diamond, Platinum, Gold, Silver, Bronze and Iron)
• Tier (I, II, III and IV)
• League points (an integer number)

Each rank consists of 4 tiers, except for Challenger, Grandmaster and Master, which only consist of 1 tier (I).

I store player data in tables, each containing information about players within a specific tier only. So my tables are named like this:

• master_league_region
• diamond_i_league_region
• diamond_ii_league_region
• etc.

The number of entries in each table can by anything from 0 up.

I tried storing all the data in a single table before (almost a million entries), but queries took about ~30 seconds to execute. Now they take about ~9 seconds, but this is still way too long.

Since I need the leaderboard to be divided into pages (each page showing 100 entries), I couldn’t really think about any other solution than making one big query for all the data.

This is ideal for paging the result, but this query takes about 9 seconds, and my goal is going below 1 or 2 seconds.

I’m stuck here for a couple of days now, and I really ran out of ideas of how to improve my query so I can still divide it into pages easily:

$sql = "SELECT * FROM challenger_league_$region
    UNION
    SELECT * FROM grandmaster_league_$region
    UNION
    SELECT * FROM master_league_$region
    UNION
    SELECT * FROM diamond_i_league_$region
    UNION
    SELECT * FROM diamond_ii_league_$region
    UNION
    SELECT * FROM diamond_iii_league_$region
    UNION
    SELECT * FROM diamond_iv_league_$region
    UNION
    SELECT * FROM platinum_i_league_$region
    UNION
    SELECT * FROM platinum_ii_league_$region
    UNION
    SELECT * FROM platinum_iii_league_$region
    UNION
    SELECT * FROM platinum_iv_league_$region
    UNION
    SELECT * FROM gold_i_league_$region
    UNION
    SELECT * FROM gold_ii_league_$region
    UNION
    SELECT * FROM gold_iii_league_$region
    UNION
    SELECT * FROM gold_iv_league_$region
    UNION
    SELECT * FROM silver_i_league_$region
    UNION
    SELECT * FROM silver_ii_league_$region
    UNION
    SELECT * FROM silver_iii_league_$region
    UNION
    SELECT * FROM silver_iv_league_$region
    UNION
    SELECT * FROM bronze_i_league_$region
    UNION
    SELECT * FROM bronze_ii_league_$region
    UNION
    SELECT * FROM bronze_iii_league_$region
    UNION
    SELECT * FROM bronze_iv_league_$region
    UNION
    SELECT * FROM iron_i_league_$region
    UNION
    SELECT * FROM iron_ii_league_$region
    UNION
    SELECT * FROM iron_iii_league_$region
    UNION
    SELECT * FROM iron_iv_league_$region
    ORDER BY case WHEN tier = 'CHALLENGER' THEN '100'
    WHEN tier = 'GRANDMASTER' THEN '101'
    WHEN tier = 'MASTER' THEN '102'
    WHEN tier = 'DIAMOND' AND rank = 'I' THEN '103'
    WHEN tier = 'DIAMOND' AND rank = 'II' THEN '104'
    WHEN tier = 'DIAMOND' AND rank = 'III' THEN '105'
    WHEN tier = 'DIAMOND' AND rank = 'IV' THEN '106'
    WHEN tier = 'PLATINUM' AND rank = 'I' THEN '107'
    WHEN tier = 'PLATINUM' AND rank = 'II' THEN '108'
    WHEN tier = 'PLATINUM' AND rank = 'III' THEN '109'
    WHEN tier = 'PLATINUM' AND rank = 'IV' THEN '110'
    WHEN tier = 'GOLD' AND rank = 'I' THEN '111'
    WHEN tier = 'GOLD' AND rank = 'II' THEN '112'
    WHEN tier = 'GOLD' AND rank = 'III' THEN '113'
    WHEN tier = 'GOLD' AND rank = 'IV' THEN '114'
    WHEN tier = 'SILVER' AND rank = 'I' THEN '115'
    WHEN tier = 'SILVER' AND rank = 'II' THEN '116'
    WHEN tier = 'SILVER' AND rank = 'III' THEN '117'
    WHEN tier = 'SILVER' AND rank = 'IV' THEN '118'
    WHEN tier = 'BRONZE' AND rank = 'I' THEN '119'
    WHEN tier = 'BRONZE' AND rank = 'II' THEN '120'
    WHEN tier = 'BRONZE' AND rank = 'III' THEN '121'
    WHEN tier = 'BRONZE' AND rank = 'IV' THEN '122'
    WHEN tier = 'IRON' AND rank = 'I' THEN '123'
    WHEN tier = 'IRON' AND rank = 'II' THEN '124'
    WHEN tier = 'IRON' AND rank = 'III' THEN '125'
    WHEN tier = 'IRON' AND rank = 'IV' THEN '126'
    ELSE '200'
    END, leaguePoints DESC
    LIMIT $startFrom, 100;";

Answer

The correct way to store the data about a single entity (say “user”) is to put it all in one table. A million rows is not a very large number of rows for modern databases. That should be the starting point for your optimizations.

In all likelihood, you just need the correct indexes on the table.

It looks like you have an ordering for the tiers. This information should be stored in separate reference tables.

Multiple tables are a bad idea for many reasons, including:

• SQL is optimized for fewer large tables rather than large numbers of smaller tables.
• Accessing all the data is quite tricky, because you need to union zillions of tables together that might change over time.
• Adding or modifying columns is tricky, because you have to change zillions of tables.
• Optimizations are hard to come by, because the data in each table is relatively small. The overhead of reading half-empty data pages might come to dominate your queries.

I would suggest that you learn about or review the principles of normalization and start over with your data model.