문제 설명
MySQL: IN(p1)은 IN(p1, p2, ...)과 다르게 작동합니까? (MySQL: Does IN(p1) function differently to IN(p1, p2, ... )?)
I've just noticed this very odd (at least it seems to me), thing...
I've made sure the queries aren't being cached, but, queries using only one value in the IN() function are super slow... for no reason I can't think of. I could put a dummy value in there but... I'd really rather not :/
SELECT Clues.* FROM Clues INNER JOIN SolutionWords
ON SolutionWords.SolutionWordID = Clues.SolutionWordID
WHERE SolutionWord COLLATE utf8_general_ci = 'TEST A'
AND Clues.CultureID IN (1);
Affected rows: 0 Time: 3.843ms
SELECT Clues.* FROM Clues INNER JOIN SolutionWords
ON SolutionWords.SolutionWordID = Clues.SolutionWordID
WHERE SolutionWord COLLATE utf8_general_ci = 'TEST B'
AND Clues.CultureID IN (1,2);
Affected rows: 0 Time: 0.141ms
SELECT Clues.* FROM Clues INNER JOIN SolutionWords
ON SolutionWords.SolutionWordID = Clues.SolutionWordID
WHERE SolutionWord COLLATE utf8_general_ci = 'TEST C'
AND Clues.CultureID IN (1);
Affected rows: 0 Time: 3.815ms
[SQL]
SELECT Clues.* FROM Clues INNER JOIN SolutionWords
ON SolutionWords.SolutionWordID = Clues.SolutionWordID
WHERE SolutionWord COLLATE utf8_general_ci = 'TEST D'
AND Clues.CultureID IN (1,2);
Affected rows: 0 Time: 0.142ms
SELECT Clues.* FROM Clues INNER JOIN SolutionWords
ON SolutionWords.SolutionWordID = Clues.SolutionWordID
WHERE SolutionWord COLLATE utf8_general_ci = 'TEST E'
AND Clues.CultureID IN (1);
Affected rows: 0 Time: 3.843ms
SELECT Clues.* FROM Clues INNER JOIN SolutionWords
ON SolutionWords.SolutionWordID = Clues.SolutionWordID
WHERE SolutionWord COLLATE utf8_general_ci = 'TEST F'
AND Clues.CultureID IN (1,2);
Affected rows: 0 Time: 0.142ms
EDIT: Changing parameter order ‑> no effect
**[SQL] SELECT Clues.* FROM Clues INNER JOIN SolutionWords ON SolutionWords.SolutionWordID = Clues.SolutionWordID WHERE Clues.CultureID IN (1) AND SolutionWord COLLATE utf8_general_ci = 'TEST 1' ;
Affected rows: 0
Time: 3.833ms**
[SQL]
SELECT Clues.* FROM Clues INNER JOIN SolutionWords ON SolutionWords.SolutionWordID = Clues.SolutionWordID WHERE SolutionWord COLLATE utf8_general_ci = 'TEST 2' AND Clues.CultureID IN (1,2);
Affected rows: 0
Time: 0.141ms
[SQL]
SELECT Clues.* FROM Clues INNER JOIN SolutionWords ON SolutionWords.SolutionWordID = Clues.SolutionWordID WHERE SolutionWord COLLATE utf8_general_ci = 'TEST 3' AND Clues.CultureID IN (1);
Affected rows: 0
Time: 3.821ms
[SQL]
SELECT Clues.* FROM Clues INNER JOIN SolutionWords ON SolutionWords.SolutionWordID = Clues.SolutionWordID WHERE SolutionWord COLLATE utf8_general_ci = 'TEST 4' AND Clues.CultureID IN (1,2);
Affected rows: 0
Time: 0.141ms
[SQL]
SELECT Clues.* FROM Clues INNER JOIN SolutionWords ON SolutionWords.SolutionWordID = Clues.SolutionWordID WHERE SolutionWord COLLATE utf8_general_ci = 'TEST 5' AND Clues.CultureID IN (1);
Affected rows: 0
Time: 3.848ms
[SQL]
SELECT Clues.* FROM Clues INNER JOIN SolutionWords ON SolutionWords.SolutionWordID = Clues.SolutionWordID WHERE SolutionWord COLLATE utf8_general_ci = 'TEST 6' AND Clues.CultureID IN (1,2);
Affected rows: 0
Time: 0.142ms
EDIT: EXPLAIN for slow query (actually, it's EXACTLY the same for the fast one as well unless I'm missing something obvious)
1 SIMPLE Clues ref fk_Clues_Cultures1,fk_Clues_SolutionWords1 fk_Clues_Cultures1 5 const 371462 Using where
1 SIMPLE SolutionWords eq_ref PRIMARY PRIMARY 4 ClueExplorer.Clues.SolutionWordID 1 Using where
1 SIMPLE Clues ref fk_Clues_Cultures1,fk_Clues_SolutionWords1 fk_Clues_Cultures1 5 const 371462 Using where
1 SIMPLE SolutionWords eq_ref PRIMARY PRIMARY 4 ClueExplorer.Clues.SolutionWordID 1 Using where
EDIT: SHOW CREATE TABLE
CREATE TABLE `Clues` (
`ClueID` int(11) NOT NULL AUTO_INCREMENT,
`SolutionWordID` int(11) NOT NULL,
`Clue` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`SolutionWordBreakup` varchar(45) DEFAULT NULL,
`SpellingID` int(11) DEFAULT NULL,
`WordFormID` int(11) DEFAULT NULL,
`GrammaticalFormID` int(11) DEFAULT NULL,
`ClueCategoryID` int(11) DEFAULT NULL,
`ClueFormatID` int(11) DEFAULT NULL,
`ClueStyleID` int(11) DEFAULT NULL,
`CultureID` int(11) DEFAULT NULL,
`Difficulty` int(11) DEFAULT NULL,
`Interestingness` int(11) DEFAULT NULL,
`ReviewDate` datetime DEFAULT NULL,
`Explanation` text,
`Citation` text,
`RelevantFrom` datetime DEFAULT NULL,
`RelevantTo` datetime DEFAULT NULL,
PRIMARY KEY (`ClueID`),
KEY `fk_Clues_Spellings` (`SpellingID`),
KEY `fk_Clues_WordForms1` (`WordFormID`),
KEY `fk_Clues_GrammaticalForms1` (`GrammaticalFormID`),
KEY `fk_Clues_ClueFormats1` (`ClueFormatID`),
KEY `fk_Clues_ClueStyles1` (`ClueStyleID`),
KEY `fk_Clues_Cultures1` (`CultureID`),
KEY `fk_Clues_ClueCategories1` (`ClueCategoryID`),
KEY `fk_Clues_SolutionWords1` (`SolutionWordID`),
CONSTRAINT `fk_Clues_ClueCategories1` FOREIGN KEY (`ClueCategoryID`) REFERENCES `ClueCategories` (`ClueCategoryID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_SolutionWords1` FOREIGN KEY (`SolutionWordID`) REFERENCES `SolutionWords` (`SolutionWordID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_ClueFormats1` FOREIGN KEY (`ClueFormatID`) REFERENCES `ClueFormats` (`ClueFormatID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_ClueStyles1` FOREIGN KEY (`ClueStyleID`) REFERENCES `ClueStyles` (`ClueStyleID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_Cultures1` FOREIGN KEY (`CultureID`) REFERENCES `Cultures` (`CultureID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_GrammaticalForms1` FOREIGN KEY (`GrammaticalFormID`) REFERENCES `GrammaticalForms` (`GrammaticalFormID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_Spellings` FOREIGN KEY (`SpellingID`) REFERENCES `Spellings` (`SpellingID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Clues_WordForms1` FOREIGN KEY (`WordFormID`) REFERENCES `WordForms` (`WordFormID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2458300 DEFAULT CHARSET=utf8
참조 솔루션
방법 1:
Matt, have you considered that your problem could be related with the order of the columns in your where clause? Could you please try (as a example):
...WHERE Clues.CultureID IN (1,2)
AND SolutionWord COLLATE utf8_general_ci = 'STRING'
And see how it goes.
Chers,
Renato
(by Matt Searles、medina)