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_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]
