[mysql] Index wordt niet gebruikt, trage query

[cyberstalker]

Ik heb een raar probleem met een query. Mysql weigert een bepaalde index te gebruiken wanneer ik een tabel koppel die helemaal niets met deze index te maken heeft.

De volgende query werkt:

SQL:

SELECT
    `callFile`.`cfID`                   AS      callfile,
    GROUP_CONCAT(
        DISTINCT(`callStates`.`name`)
            ORDER BY `callStates`.`cstID`
    )                                   AS      callstates,
    GROUP_CONCAT(
        DISTINCT(`callQuestions`.`long`),
        '/',
        (
            SELECT
                GROUP_CONCAT(
                    `callQuestionOptions`.`value`
                    ORDER BY `callQuestionOptions`.`fk_cqID`
                    SEPARATOR '/'
                )
            FROM
                `callQuestionOptions`
            WHERE
                `callQuestionOptions`.`fk_cqID` =   `callQuestions`.`cqID`
        )
            ORDER BY `callQuestions`.`cqID`
    )                                   AS      questions,
    COUNT(DISTINCT(`callSheet`.`csID`)) AS      result_count
FROM
    `callFile`
JOIN
    `callStates`
ON
    `callFile`.`cfID`                   =       `callStates`.`fk_cfID`
JOIN
(
    `callSheet`,
    `mScoreCard`
)
ON
    `callFile`.`cfID`                   =       `callSheet`.`fk_cfID`                                       AND
    `callSheet`.`fk_scID`               =       `mScoreCard`.`mSCid`
JOIN
    `callQuestions`
ON
    callFile.`cfID`                     =       `callQuestions`.`fk_cfID`
WHERE
    `mScoreCard`.`date`                                                                                     BETWEEN
    @start_date                         AND     @end_date                                                   AND
    `callFile`.`fk_mPid`                =       @project_id
GROUP BY
    `callFile`.`cfId`
ORDER BY
    `callFile`.`cfID`;

en geeft de volgende explain

code:

+----+--------------------+---------------------+--------+-----------------+---------+---------+----------------------------+------+-----------------------------+
| id | select_type        | table               | type   | possible_keys   | key     | key_len | ref                        | rows | Extra                       |
+----+--------------------+---------------------+--------+-----------------+---------+---------+----------------------------+------+-----------------------------+
|  1 | PRIMARY            | callFile            | ref    | PRIMARY,fk_mPid | fk_mPid | 4       | const                      |    2 | Using where; Using filesort | 
|  1 | PRIMARY            | callStates          | ref    | fk_cfID         | fk_cfID | 4       | tsu_dev.callFile.cfID      |    3 |                             | 
|  1 | PRIMARY            | callQuestions       | ref    | fk_cfID         | fk_cfID | 4       | tsu_dev.callStates.fk_cfID |    6 | Using where                 | 
|  1 | PRIMARY            | callSheet           | ref    | fk_cfID,fk_scID | fk_cfID | 4       | tsu_dev.callStates.fk_cfID |   77 | Using where                 | 
|  1 | PRIMARY            | mScoreCard          | eq_ref | PRIMARY         | PRIMARY | 4       | tsu_dev.callSheet.fk_scID  |    1 | Using where                 | 
|  2 | DEPENDENT SUBQUERY | callQuestionOptions | ref    | fk_cqID         | fk_cqID | 4       | tsu_dev.callQuestions.cqID |   16 |                             | 
+----+--------------------+---------------------+--------+-----------------+---------+---------+----------------------------+------+-----------------------------+
6 rows in set (0.01 sec)

Ik wil echter de resultaten verder beperken met behulp van een tweetal tabellen. Ik join deze tabellen op callSheet, de query wordt dan dit:

SQL:

SELECT
    `callFile`.`cfID`                   AS      callfile,
    GROUP_CONCAT(
        DISTINCT(`callStates`.`name`)
            ORDER BY `callStates`.`cstID`
    )                                   AS      callstates,
    GROUP_CONCAT(
        DISTINCT(`callQuestions`.`long`),
        '/',
        (
            SELECT
                GROUP_CONCAT(
                    `callQuestionOptions`.`value`
                    ORDER BY `callQuestionOptions`.`fk_cqID`
                    SEPARATOR '/'
                )
            FROM
                `callQuestionOptions`
            WHERE
                `callQuestionOptions`.`fk_cqID` =   `callQuestions`.`cqID`
        )
            ORDER BY `callQuestions`.`cqID`
    )                                   AS      questions,
    COUNT(DISTINCT(`callSheet`.`csID`)) AS      result_count
FROM
    `callFile`
JOIN
    `callStates`
ON
    `callFile`.`cfID`                   =       `callStates`.`fk_cfID`
JOIN
(
    `callSheet`,
    `user`,
    `mPerson2department`,
    `mScoreCard`
)
ON
    `callFile`.`cfID`                   =       `callSheet`.`fk_cfID`                                       AND
    `callSheet`.`fk_scID`               =       `mScoreCard`.`mSCid`                                        AND
    `callSheet`.`owner_uid`             =       `user`.`uid`                                                AND
    `user`.`fk_pid`                     =       `mPerson2department`.`fk_pid`
JOIN
    `callQuestions`
ON
    callFile.`cfID`                     =       `callQuestions`.`fk_cfID`
WHERE
    ISNULL(@show_depts)                 OR      FIND_IN_SET(`mPerson2department`.`fk_did`, @show_depts)     AND
    `mScoreCard`.`date`                                                                                     BETWEEN
    @start_date                         AND     @end_date                                                   AND
    `callFile`.`fk_mPid`                =       @project_id
GROUP BY
    `callFile`.`cfId`
ORDER BY
    `callFile`.`cfID`;

De explain wordt dan het volgende:

code:

+----+--------------------+---------------------+--------+---------------------------+---------+---------+-------------------------------+------+---------------------------------+
| id | select_type        | table               | type   | possible_keys             | key     | key_len | ref                           | rows | Extra                           |
+----+--------------------+---------------------+--------+---------------------------+---------+---------+-------------------------------+------+---------------------------------+
|  1 | PRIMARY            | callQuestions       | ALL    | fk_cfID                   | NULL    | NULL    | NULL                          |   50 | Using temporary; Using filesort | 
|  1 | PRIMARY            | callFile            | eq_ref | PRIMARY                   | PRIMARY | 4       | tsu_dev.callQuestions.fk_cfID |    1 |                                 | 
|  1 | PRIMARY            | callStates          | ref    | fk_cfID                   | fk_cfID | 4       | tsu_dev.callQuestions.fk_cfID |    3 |                                 | 
|  1 | PRIMARY            | callSheet           | ref    | fk_cfID,fk_scID,owner_uid | fk_cfID | 4       | tsu_dev.callFile.cfID         |   77 | Using where                     | 
|  1 | PRIMARY            | user                | eq_ref | PRIMARY,fk_pid            | PRIMARY | 4       | tsu_dev.callSheet.owner_uid   |    1 |                                 | 
|  1 | PRIMARY            | mPerson2department  | ref    | fk_pid                    | fk_pid  | 4       | tsu_dev.user.fk_pid           |   15 |                                 | 
|  1 | PRIMARY            | mScoreCard          | eq_ref | PRIMARY                   | PRIMARY | 4       | tsu_dev.callSheet.fk_scID     |    1 |                                 | 
|  2 | DEPENDENT SUBQUERY | callQuestionOptions | ref    | fk_cqID                   | fk_cqID | 4       | tsu_dev.callQuestions.cqID    |   16 |                                 | 
+----+--------------------+---------------------+--------+---------------------------+---------+---------+-------------------------------+------+---------------------------------+
8 rows in set (0.00 sec)

Zoals je ziet wordt in de tweede query ineens de index op callQuestions niet meer gebruikt. Dit vind ik erg vreemd omdat ik niets verander aan hoe de join met callQuestions werkt.

Ik heb 'FORCE INDEX' al geprobeerd, maar zonder resultaat. Het gekke is dat fk_cfID wel bij possible_keys staat maar vervolgens niet wordt gebruikt.

Hoe kan ik ervoor zorgen dat mysql netjes de index gebruikt?

[Voutloos]

Schrijf eerst eens alle joins netjes 1 voor 1 ipv opeens over te stappen op "JOIN (A,B,C,D) ON ...".
De ',' Join operator kan je het best vermijden, gebruik liever een join operator welke explicieter is en gebruik geen haakjes bij het joinen indien niet nodig.

offtopic:
Voorkeuren kunnen verschillen, maar je querystyle leest imo heel slecht. Verder zijn acties als het gebruiken van een alias welke gelijk is aan een tabelnaam op een hoofdletter na ook alleen maar verwarrend.

[ Voor 36% gewijzigd door Voutloos op 11-01-2008 15:46 ]

[justmental]

Het zou die OR kunnen zijn, heb je wel bedoeld om die op hezelfde niveau te zetten als de beperkende clausules in je where?
Ik zou (isnull......or find...) and verwachten.

[cyberstalker]

Voutloos schreef op vrijdag 11 januari 2008 @ 15:44:
Schrijf eerst eens alle joins netjes 1 voor 1 ipv opeens over te stappen op "JOIN (A,B,C,D) ON ...".
De ',' Join operator kan je het best vermijden, gebruik liever een join operator welke explicieter is en gebruik geen haakjes bij het joinen indien niet nodig.

Hmm, ik dacht dat je in die join alleen tabellen kunt aanspreken die in de from clause of in de bewuste join clause staan. Misschien heb ik dat niet helemaal goed begrepen...

offtopic:
Voorkeuren kunnen verschillen, maar je querystyle leest imo heel slecht. Verder zijn acties als het gebruiken van een alias welke gelijk is aan een tabelnaam op een hoofdletter na ook alleen maar verwarrend.

Dat is idd persoonlijke voorkeur. Met die aliases heb je gelijk. Punt is dat ik de database niet zelf heb opgezet en dat ik de tabelnamen niet logisch vind.

justmental schreef op vrijdag 11 januari 2008 @ 15:45:
Het zou die OR kunnen zijn, heb je wel bedoeld om die op hezelfde niveau te zetten als de beperkende clausules in je where?
Ik zou (isnull......or find...) and verwachten.

Het is de OR inderdaad. Haakjes er omheen heeft het probleem opgelost. Hartstikke bedankt!