· 8 years ago · Aug 05, 2018, 04:12 AM
1select query join tables
2SELECT pid, count(*) WordMatchCount
3 FROM indx_0
4 WHERE wid in ( 294, 20591, 330 )
5 group by pid
6 order by WordMatchCount desc
7 limit 1000
8
9CREATE TABLE IF NOT EXISTS `windex` (
10 `word` varchar(64) NOT NULL,
11 `wid` int(10) NOT NULL AUTO_INCREMENT
12 PRIMARY KEY (`wid`),
13 UNIQUE KEY `word` (`word`)
14) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=834922 ;
15
16CREATE TABLE IF NOT EXISTS `indx_0` (
17 `wid` int(7) NOT NULL,
18 `pid` int(7) NOT NULL,
19 UNIQUE KEY `wid` (`wid`,`pid`)
20) ENGINE=MyISAM DEFAULT CHARSET=latin1;
21
22SELECT i0.pid, count(*) WordMatchCount
23FROM indx_0 AS i0
24LEFT JOIN windex AS wi ON (wi.wid = i0.wid)
25WHERE wi.word REGEXP '(.*)ddr(.*)|(.*)memory(.*)|(.*)card(.*)'
26group by i0.pid
27order by WordMatchCount desc