Rick James Wrote:
-------------------------------------------------------
> Aha, you are not searching on values of 'val'. My
> apologies.
>
> Here's another on to try with your particular
> data:
>
> (
> SELECT itemId FROM DataMap WHERE dataId=1
> UNION
> SELECT itemId FROM DataMap WHERE dataId=2
> UNION
> SELECT itemId FROM DataMap WHERE dataId=3
> )
> GROUP BY itemId
> HAVING COUNT(dataId)=3
> LIMIT 20
> )
>
> The rationale behind trying that is that UNION is
> often faster than OR.
Yup, have tried this one as well, is even slower than the others :(
>
> Perhaps the EXPLAIN would give us some more clues;
> please provide
> * SHOW CREATE TABLE tbl\G
> * SHOW TABLE STATUS LIKE 'tbl'\G
> * EXPLAIN SELECT ...\G
> and surround them with [ code ] and [ / code ]
>
> Also, if your queries have hit the slowlog, what
> is the value of "Rows Affected"?
Here is the requested info:
CREATE TABLE `dataMap` (
`itemId` int(11) unsigned NOT NULL,
`dataId` int(11) unsigned NOT NULL,
UNIQUE KEY `dataId` (`itemId`,`dataId`),
KEY `dataId_2` (`dataId`,`itemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
dataMap InnoDB 10 Compact 1230345 31 38338560 0 26787840 1792016384 NULL 2009-04-09 01:25:56 NULL NULL utf8_unicode_ci NULL
EXPLAIN SELECT SQL_NO_CACHE itemId
FROM `dataMap`
WHERE dataId=1000 or dataId=10000 or dataId=100000
GROUP BY itemId
HAVING COUNT(dataId)=3
LIMIT 20
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dataMap index dataId_2 dataId 8 NULL 46 Using where; Using index
Found something strange during explain: if I do SELECT * instead of SELECT itemId, Explain says 'Using Where' instead of 'Using Where, Index'
Does that make sense? Not to me.
And no, it does not hit the slow query log, which is set to 1second, but can make it hit the log ofc.
Edited 2 time(s). Last edit at 04/09/2009 05:42AM by József Rekedt-Nagy.