AW4dG83m

1-- ----------------------------
2-- MariaDB 10.1 Table structures (simplified names and columns for this case)
3-- ----------------------------
4-- Contains the main information rows - this normally has 100M+ rows
5DROP TABLE IF EXISTS `a`;
6CREATE TABLE `a` (
7  `id` int(11) NOT NULL AUTO_INCREMENT,
8-- Other columns would normally be here but were removed to simplify this
9  PRIMARY KEY (`id`)
10) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
11
12
13-- Contains the text content for a - This has the same amount of rows as `a`
14DROP TABLE IF EXISTS `a_content`;
15CREATE TABLE `a_content` (
16  `id_a` int(11) NOT NULL AUTO_INCREMENT,
17  `title` varchar(2000) NOT NULL,
18  `description` varchar(2000) NOT NULL,
19  PRIMARY KEY (`id_a`),
20  FULLTEXT `f` (`title`,`description`)
21) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
22
23-- Contains whether an `a` ID is hidden for a user ID - This normally has 30M+ rows
24DROP TABLE IF EXISTS `a_hidden`;
25CREATE TABLE `a_hidden` (
26  `id_a` int(11) NOT NULL AUTO_INCREMENT,
27  `user` int(11) NOT NULL,
28  PRIMARY KEY (`id_a`,`user`)
29) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
30
31-- Contains an `a`'s country ID's - This normally has 300M+ rows
32DROP TABLE IF EXISTS `a_country`;
33CREATE TABLE `a_country` (
34  `id_a` int(11) NOT NULL AUTO_INCREMENT,
35  `country` tinyint(4) NOT NULL,
36  PRIMARY KEY (`id_a`,`country`)
37) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
38
39
40-- ----------------------------
41-- Sample Records of a
42-- ----------------------------
43INSERT INTO `a` VALUES ('1');
44INSERT INTO `a` VALUES ('2');
45INSERT INTO `a` VALUES ('3');
46INSERT INTO `a` VALUES ('4');
47INSERT INTO `a` VALUES ('5');
48INSERT INTO `a` VALUES ('6');
49INSERT INTO `a` VALUES ('7');
50INSERT INTO `a` VALUES ('8');
51INSERT INTO `a` VALUES ('9');
52INSERT INTO `a` VALUES ('10');
53
54INSERT INTO `a_content` VALUES ('1','aa sadd','aa');
55INSERT INTO `a_content` VALUES ('2','aasdadd sads','aa');
56INSERT INTO `a_content` VALUES ('3','aa daa','aaf df');
57INSERT INTO `a_content` VALUES ('4','aa sad','aadf ');
58INSERT INTO `a_content` VALUES ('5','aa v','aaxcv');
59INSERT INTO `a_content` VALUES ('6','aa c','aavv');
60INSERT INTO `a_content` VALUES ('7','aa df','aaaa');
61INSERT INTO `a_content` VALUES ('8','aa df','aa s');
62INSERT INTO `a_content` VALUES ('9','aa vvv','a a');
63INSERT INTO `a_content` VALUES ('10','ss','aaaa');
64
65INSERT INTO `a_hidden` VALUES ('1','1');
66INSERT INTO `a_hidden` VALUES ('3','1');
67INSERT INTO `a_hidden` VALUES ('5','1');
68INSERT INTO `a_hidden` VALUES ('7','1');
69INSERT INTO `a_hidden` VALUES ('9','1');
70
71INSERT INTO `a_country` VALUES ('1',1);
72INSERT INTO `a_country` VALUES ('1',2);
73INSERT INTO `a_country` VALUES ('2',1);
74INSERT INTO `a_country` VALUES ('3',2);
75INSERT INTO `a_country` VALUES ('4',4);
76INSERT INTO `a_country` VALUES ('5',112);
77INSERT INTO `a_country` VALUES ('6',33);
78INSERT INTO `a_country` VALUES ('7',44);
79INSERT INTO `a_country` VALUES ('8',66);
80INSERT INTO `a_country` VALUES ('9',88);
81INSERT INTO `a_country` VALUES ('10',99);
82	
83# Extremely slow and takes hours even though it's using indexes all over. If I remove one join it's instantly fast though
84SELECT a.id
85FROM  `a` 
86LEFT JOIN a_hidden ON a_hidden.id_a = a.id && a_hidden.user=1
87LEFT JOIN a_content ON a_content.id_a = a.id
88INNER JOIN a_country ON a_country.id_a = a.id && a_country.country IN (1,2,3) 
89WHERE a_hidden.id_a IS NULL && MATCH (a_content.title, a_content.description) AGAINST ('search' IN BOOLEAN MODE)
90ORDER BY a.id DESC 
91LIMIT 0 , 50