understanding mysql explain

Paul:

eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE index.

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:

SELECT * FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process ref_table:

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_tableWHERE ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;

These are copied verbatim from the MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

If you could post your query that is taking forever, I could help pinpoint what is slowing it down. Also, please specify what your definition of forever is. Also, if you could provide your "SHOW CREATE TABLE xxx;" statements for these tables, I could help in optimizing your query as much as possible.

What jumps out at me immediately as a possible point of improvement is the "Using temporary; Using filesort;". This means that a temporary table was created to satisfy the query (not necessarily a bad thing), and that the GROUP BY/ORDER BY you designated could not be retrieved from an index, thus resulting in a filesort.

You query seems to process (244 * 298 * 152) = 11,052,224 records, which according to Using temporary; Using filesort need to be sorted.

This can take long.

If you post your query here, we probably will be able to optimize it somehow.

Update:

You query indeed does a number of nested loops and seems to yield lots of values which need to be sorted then.

Could you please run the following query:

SELECT  COUNT(*)FROM    `yourock_achievement`INNER JOIN        `yourock_achiever`ON       `yourock_achievement`.`id` = `yourock_achiever`.`achievement_id`INNER JOIN        `yourock_alias`ON      `yourock_achiever`.`alias_id` = `yourock_alias`.`id`INNER JOIN        `yourock_achiever` T4ON      `yourock_alias`.`id` = T4.`alias_id`INNER JOIN        `yourock_achievement` T5ON      T4.`achievement_id` = T5.`id`INNER JOIN        `yourock_achiever` T6ON      T5.`id` = T6.`achievement_id`WHERE        T6.`alias_id` = 6