| Bug #61031 | MySQL not using concatenated index. | ||
|---|---|---|---|
| Submitted: | 2 May 2011 20:15 | Modified: | 5 May 2011 18:10 |
| Reporter: | R M | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.1.42 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 May 2011 20:15]
R M
[3 May 2011 4:17]
MySQL Verification Team
Have you tried SELECT .. FROM my_mrg_tbl FORCE INDEX (src__oct1) WHERE ... ? http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
[3 May 2011 12:53]
R M
Thanks for your time and response. Yes, I already tried to force the hint like how you described; however, my query uses a subquery. Now when I explain the subquery with the index hint, Explain tells me that the index will be used. But when I do the entire query (which includes the subquery) the query itself nor the explain plan never returns back to me. Ive read that Explain has issues working with subqueries or derived tables. What can I do to work around this problem? Thank you.
[3 May 2011 13:07]
R M
Here is my subquery below.
We have 2 separate databases. The original database uses individual
indexes on src_oct*, dst_oct*, etc and takes about 53 seconds to
run this query (non-cached data) - we have 6+ billion rows (MyISAM).
The new database has a multiple concatenated indexes as such:
(datetime, src__oct1, src__oct2, src__oct3, src__oct4)
(datetime, dst__oct1, dst__oct2, dst__oct3, dst__oct4)
etc. But when the same query runs here it never returns.
explain
Select max(datetime) as LatestDate,orig__oct1,orig__oct2,orig__oct3,orig__oct4,src__oct1,src__oct2,src__oct3,src__oct4,dst__oct1,dst__oct2,dst__oct3,dst__oct4, rule_uid,service,`action`, count(num) as Hits
from fwlogger_mrg
where datetime between '2010-01-01' and '2010-11-10' and
( (((dst__oct1='67' or '67'='0') and
(dst__oct2='57' or '57'='0') and
(dst__oct3='136' or '136'='0') and
(dst__oct4='0' or '0'='0')) or
((dst__oct1='204' or '204'='0') and
(dst__oct2='109' or '109'='0') and
(dst__oct3='181' or '181'='0') and
(dst__oct4='0' or '0'='0')) or
((dst__oct1='155' or '155'='0') and
(dst__oct2='195' or '195'='0') and
(dst__oct3='64' or '64'='0') and
(dst__oct4='0' or '0'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='5' or '5'='0') and
(dst__oct4='6' or '6'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='5' or '5'='0') and
(dst__oct4='6' or '6'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
Select LatestDate,Gateway, Source, Dest,Rule,`action`, Hits
from (select LatestDate,cast(concat_ws
(".",orig__oct1,orig__oct2,orig__oct3,orig__oct4)as char)as
"Gateway",cast(concat_ws
(".",src__oct1,src__oct2,src__oct3,src__oct4) as char) as
"Source",cast(concat_ws
(".",dst__oct1,dst__oct2,dst__oct3,dst__oct4)as char) as
"Dest", rule_uid as Rule,`action`, Hits
from (
Select max(datetime) as
LatestDate,orig__oct1,orig__oct2,orig__oct3,orig__oct4,src__oct1,src__oct2,src__oct3,src__oct4,dst__oct1,dst__oct2,dst__oct3,dst__oct4,
rule_uid,`action`, count(num) as Hits
from our_mrg_tbl
where datetime between '2011-01-01 00:00' and '2011-03-26 22:59' and
( (((dst__oct1='206' or '206'='0') and
(dst__oct2='200' or '200'='0') and
(dst__oct3='176' or '176'='0') and
(dst__oct4='0' or '0'='0')) or
((dst__oct1='206' or '206'='0') and
(dst__oct4='5' or '5'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='3' or '3'='0') and
(dst__oct4='5' or '5'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='3' or '3'='0') and
(dst__oct4='5' or '5'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='3' or '3'='0') and
(dst__oct4='5' or '5'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='3' or '3'='0') and
(dst__oct4='5' or '5'='0')) or
((dst__oct1='1' or '1'='0') and
(dst__oct2='2' or '2'='0') and
(dst__oct3='3' or '3'='0') and
(dst__oct4='5' or '5'='0'))))
and not
(((src__oct1='1' or '1'='0') and
(src__oct2='2' or '2'='0') and
(src__oct3='3' or '3'='0') and
(src__oct4='5' or '5'='0')) or
((src__oct1='1' or '1'='0') and
(src__oct2='2' or '2'='0') and
(src__oct3='3' or '3'='0') and
(src__oct4='5' or '5'='0')))
group by
orig__oct1,orig__oct2,orig__oct3,orig__oct4,src__oct1,src__oct2,src__oct3,src__oct4,dst__oct1,dst__oct2,dst__oct3,dst__oct4,
rule_uid,`action`)raw
where `action` in ('accept', 'encrypt')) fulllist
left join gateway on concat_ws(".",gateway_oct1, gateway_oct2,
gateway_oct3,gateway_oct4)=Gateway
where gateway_funct='DCA'
order by LatestDate
limit 59900
;
[4 May 2011 15:39]
R M
Any updates on this? Thank you.
[5 May 2011 17:29]
Sveta Smirnova
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL. To consider this MySQL bug we need you prove it: i.e. create small test case similar to your large query which demonstrates index is not used properly.
[5 May 2011 17:35]
R M
Thanks for taking 2 days to tell me this. If this is not a bug, then what would be the right terminology? Which forum do you recommend that I list this under? Let me get this straight - I have to pay support to get a very basic MySQL functionality to work correctly .. surely you must be kidding me.
[5 May 2011 18:10]
R M
Small case or large case - this does not work the way it is suppose to work - this is a bug in your system! If we cannot get basic functionality from MySQL then we will look to other products for results.
