CODEWITHSUNDEEP
mysqlquery-performance
Matt Searles
Matt Searles

Reputation: 2765

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

Upvotes: 0

Views: 107

Answers (1)

medina
medina

Reputation: 8159

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

Upvotes: 1

Related Questions