> differents values on the same parameter behaves different using different indexes
Quite possibly. I have seen at least 6 different EXPLAIN plans for a single SELECT (with diff constants).
Is MySQL doing the "wrong thing"? Usually not.
Example: SELECT y FROM tbl WHERE x = 3; INDEX(x)
Case 1: Only a few rows have x=3.
It will use the index; this will be "right".
Case 2: Half the table has x=3.
It will ignore the index. To use the index means walking through the index, reaching over to the data repeatedly to find `y`. This is likely to be more work than simply walking through the data, skipping the rows where x != 3.
Case 3: There is a grey area. 15% of the rows have x=3 -- which way will be faster? MySQL cannot tell, so it picks Case 1 or Case 2; sometimes it may pick the "wrong" one.
SELECT RANK.SITE, MAX(RANK.TIPO_EDICION) AS TIPO_EDICION,
MAX(RANK.EDICION) AS EDICION, MAX(RANK.SECCION) AS SECCION,
MAX(RANK.AUTOR) AS AUTOR, MAX(RANK.TIPO_CONTENIDO) AS TIPO_CONTENIDO,
0 AS CANTIDAD_HITS, 0 AS CANTIDAD_COMENTARIOS,
SUM(SUMA_VALORACIONES) AS VALORACIONES,
SUM(CANTIDAD_VALORACIONES) AS CANTIDAD_VALORACIONES,
0 AS CANTIDAD_RECOMENDACIONES, RANK.URL
FROM TFS_STATISTICS AS RANK USE INDEX (TFS_STATISTICS_RANKING5)
WHERE RANK.SITE='NewsPaper'
AND RANK.SECCION='futbol'
AND RANK.TIMESLOT>='2011-12-01 00:01:00'
AND RANK.TIMESLOT<='2011-12-15 00:01:00'
AND CANTIDAD_VALORACIONES > 0
GROUP BY RANK.SITE, RANK.URL
ORDER BY VALORACIONES desc, RANK.URL
LIMIT 10;
PRIMARY KEY ( URL , SITE , TIPO_EDICION , EDICION , TIMESLOT )
INDEX TFS_STATISTICS_VALORACIONES (CANTIDAD_VALORACIONES, SITE, TIPO_EDICION, EDICION, SECCION)
INDEX TFS_STATISTICS_RANKING_SECCION_VALORACION (SITE, SECCION, URL, CANTIDAD_VALORACIONES, TIMESLOT)
Well, all those indexes are useless. And TFS_STATISTICS_RANKING5 is not mentioned. PLEASE just do SHOW CREATE TABLE.
Either of these would be the best index for that query:
INDEX (SITE, SECCION, TIMESLOT), or
INDEX (SECCION, SITE, TIMESLOT)
But, as I say, I could be fooled, as in Case 2.
General rule in laying out the fields for an INDEX:
1. List the fields that are tested in WHERE with "=".
2. Add one more field:
2a. If you have a range test (BETWEEN, >=, ...), the field in it, or
2b. If you have a GROUP BY, its fields, or
2c. If you have an ORDER BY, its fields
In your case, there are likely to be two sorts -- one for GROUP BY, one for ORDER BY. These will be sorting lots of rows. Only after the sorts can it look at the LIMIT.
Sometimes (not your case),
ORDER BY p, q LIMIT 10
will effectively use this index, regardless of the WHERE clause:
INDEX(p, q)
Again, MySQL could guess "wrong" on whether to optimize based on the WHERE clause or on the ORDER BY.
In your case, the GROUP BY and the ORDER BY are different, so it cannot combine them to make only one sort, or even use INDEX(p,q) to get rid of that sort.
If you care to discuss this, or other, queries further, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?