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_games
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_id_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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
