SEO

Here is a query I'm trying to run. It doesn't look like it is overly
complex and, granted, it's joining on a few tables, I wouldn't imagine it
would take 6.5s to execute.

SELECT
TRIM( users.username ) AS username,
TRIM( games.game_name ) AS game_name,
CASE
WHEN 0 != UNIX_TIMESTAMP( MAX( collections.modified )) THEN TRIM( MAX(
collections.modified ))
ELSE TRIM( MAX( collections.created ))
END AS modified,
TRIM( users.hide_inventory ) AS hide_inventory
FROM
collections
INNER JOIN users ON users.id = collections.user_id
INNER JOIN game_pieces ON game_pieces.id = collections.game_piece_id
INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id
INNER JOIN games ON games.id = games_sets.game_id
GROUP BY
users.username,
games.game_name
ORDER BY
users.username;

167 rows in set (6.49 sec)

Table collections has 76,328 rows
Table users has 291 rows
Table game_pieces has 5,491 rows
Table games_sets has 29 rows
Table games has 3 rows

Table games has a FK for games_sets which has a FK for game_pieces.

All are InnoDB tables on a MySQL 5.x database

Here is the same query EXPLAINed:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: games
type: index
possible_keys: PRIMARY
key: ix_games_GameName
key_len: 102
ref: NULL
rows: 3
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: games_sets
type: ref
possible_keys: PRIMARY,ix_games_sets_games_sets,ix_games_sets_gam es
key: ix_games_sets_games
key_len: 8
ref: cake_communal_haven.games.id
rows: 4
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: game_pieces
type: ref
possible_keys: PRIMARY,ix_game_pieces_games_set_id,ix_game_pieces _set_piece
key: ix_game_pieces_games_set_id
key_len: 9
ref: cake_communal_haven.games_sets.id
rows: 127
Extra: Using where; Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: collections
type: ref
possible_keys:
ix_collections_game_piece_id,ix_collections_user_i d_game_piece_id
,ix_collections_user_id
key: ix_collections_game_piece_id
key_len: 8
ref: cake_communal_haven.game_pieces.id
rows: 23
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: users
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 22
ref: cake_communal_haven.collections.user_id
rows: 1
Extra:
5 rows in set (0.00 sec)

In the EXPLAIN, it doesn't look like any indexes are being used in rows 4
and 5. Is that right? I'm looking at all the columns that are being joined
and they all do have indexes.

If I take out the tables game_pieces, games_sets and games and remove
"TRIM( games.game_name ) AS game_name" from the select, the execution time
goes down to 1.9 seconds so it seems as if it isn't using an appropriate
index from either/any of those tables.

Is there anything I can do to speed this query up? Or is the joining of
76k+ rows to 5k+ rows (plus the other tables) really going to slow things
down that significantly? I can't imagine that it would because I'm sure
there are alot of other people using MySQL on much larger databases.

thnx,
Christoph

Make sure that all joined fields are of identical types, otherwise the
query executor must cast each and every join value, which may be
affecting the query time...

Jay

Chris Boget wrote:
> Here is a query I'm trying to run. It doesn't look like it is overly
> complex and, granted, it's joining on a few tables, I wouldn't imagine
> it would take 6.5s to execute.
>
> SELECT
> TRIM( users.username ) AS username,
> TRIM( games.game_name ) AS game_name,
> CASE
> WHEN 0 != UNIX_TIMESTAMP( MAX( collections.modified )) THEN TRIM( MAX(
> collections.modified ))
> ELSE TRIM( MAX( collections.created ))
> END AS modified,
> TRIM( users.hide_inventory ) AS hide_inventory
> FROM
> collections
> INNER JOIN users ON users.id = collections.user_id
> INNER JOIN game_pieces ON game_pieces.id = collections.game_piece_id
> INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id
> INNER JOIN games ON games.id = games_sets.game_id
> GROUP BY
> users.username,
> games.game_name
> ORDER BY
> users.username;
>
> 167 rows in set (6.49 sec)
>
> Table collections has 76,328 rows
> Table users has 291 rows
> Table game_pieces has 5,491 rows
> Table games_sets has 29 rows
> Table games has 3 rows
>
> Table games has a FK for games_sets which has a FK for game_pieces.
>
> All are InnoDB tables on a MySQL 5.x database
>
> Here is the same query EXPLAINed:
>
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: games
> type: index
> possible_keys: PRIMARY
> key: ix_games_GameName
> key_len: 102
> ref: NULL
> rows: 3
> Extra: Using index; Using temporary; Using filesort
> *************************** 2. row ***************************
> id: 1
> select_type: SIMPLE
> table: games_sets
> type: ref
> possible_keys: PRIMARY,ix_games_sets_games_sets,ix_games_sets_gam es
> key: ix_games_sets_games
> key_len: 8
> ref: cake_communal_haven.games.id
> rows: 4
> Extra: Using index
> *************************** 3. row ***************************
> id: 1
> select_type: SIMPLE
> table: game_pieces
> type: ref
> possible_keys: PRIMARY,ix_game_pieces_games_set_id,ix_game_pieces _set_piece
> key: ix_game_pieces_games_set_id
> key_len: 9
> ref: cake_communal_haven.games_sets.id
> rows: 127
> Extra: Using where; Using index
> *************************** 4. row ***************************
> id: 1
> select_type: SIMPLE
> table: collections
> type: ref
> possible_keys:
> ix_collections_game_piece_id,ix_collections_user_i d_game_piece_id
> ,ix_collections_user_id
> key: ix_collections_game_piece_id
> key_len: 8
> ref: cake_communal_haven.game_pieces.id
> rows: 23
> Extra:
> *************************** 5. row ***************************
> id: 1
> select_type: SIMPLE
> table: users
> type: ref
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 22
> ref: cake_communal_haven.collections.user_id
> rows: 1
> Extra:
> 5 rows in set (0.00 sec)
>
> In the EXPLAIN, it doesn't look like any indexes are being used in rows
> 4 and 5. Is that right? I'm looking at all the columns that are being
> joined and they all do have indexes.
>
> If I take out the tables game_pieces, games_sets and games and remove
> "TRIM( games.game_name ) AS game_name" from the select, the execution
> time goes down to 1.9 seconds so it seems as if it isn't using an
> appropriate index from either/any of those tables.
>
> Is there anything I can do to speed this query up? Or is the joining of
> 76k+ rows to 5k+ rows (plus the other tables) really going to slow
> things down that significantly? I can't imagine that it would because
> I'm sure there are alot of other people using MySQL on much larger
> databases.
>
> thnx,
> Christoph
>
>

> Make sure that all joined fields are of identical types, otherwise the
> query executor must cast each and every join value, which may be
> affecting the query time...

Can you even define fields having foreign keys to be of a different type?
Anyway, taking a look at my JOIN

>> INNER JOIN users ON users.id = collections.user_id
>> INNER JOIN game_pieces ON game_pieces.id = collections.game_piece_id
>> INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id
>> INNER JOIN games ON games.id = games_sets.game_id

users.id is VARCHAR(20) and is a FK to collections.user_id which is
VARCHAR(20)

game_pieces.id is BIGINT(20) and is a FK to collections.game_piece_id which
is BIGINT(20)

games_sets.id is BIGINT(20) and is a FK to game_pieces.games_set_id which is
BIGINT(20)

games.id is BIGINT(20) and is a FK to games_sets.game_id which is BIGINT(20)

thnx,
Christoph