MySQL Forums
Forum List  »  Optimizer & Parser

Re: Problem with a query performance
Posted by: Rick James
Date: January 05, 2012 10:41AM

> 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?

Options: ReplyQuote


Subject
Views
Written By
Posted
2560
January 04, 2012 01:43PM
Re: Problem with a query performance
1099
January 05, 2012 10:41AM
1023
January 06, 2012 12:36PM
1017
January 07, 2012 10:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.