If someone could suggest some advice/guidance I would be very
grateful. I'm trying to determine why the following SELECT query table
locks the "bad_behavior" table referenced the query.
'bad_behavior' is MyISAM
'c' is InnoDB
'a' is InnoDB
select item_p from (select inet_ntoa(ip) as
item_p,sum(if(class_factor>0.75,1,0)) as info,count(*) as count from c
join a on c.mid=a.mid where c.date > subdate(now(),interval 6 hour) &&
ip not in (select address from bad_behavior where score >= 6 ) group
by ip) as t1 where info >= 5 && info/count >= 0.75
The befuddling part is that the bad_behavior table is table locked
(preventing updates/inserts) until the query above ends. The version
of MySQL is 5.0.27.
Any help is greatly appreciated.
Thank you in advance.