0

I am working on a search app that queries a large amount of info (100M+ rows) with multiple filters that can be used and I am having trouble getting it to perform when multiple filters are combined. When I have three or more joins the query takes hours to finish. Is there any way to make multiple joins a lot faster or am I doing something wrong? The following SQL fiddle explains the predicament:

http://sqlfiddle.com/#!9/db1eab8/1

For reference the table structure and slow query is also below:

-- ----------------------------
-- MariaDB 10.1 Table structures (simplified names and columns for this case)
-- ----------------------------
-- Contains the main information rows - this normally has 100M+ rows
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
-- Other columns would normally be here but were removed to simplify this
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


-- Contains the text content for a - This has the same amount of rows as `a`
DROP TABLE IF EXISTS `a_content`;
CREATE TABLE `a_content` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(2000) NOT NULL,
  `description` varchar(2000) NOT NULL,
  PRIMARY KEY (`id_a`),
  FULLTEXT `f` (`title`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains whether an `a` ID is hidden for a user ID - This normally has 30M+ rows
DROP TABLE IF EXISTS `a_hidden`;
CREATE TABLE `a_hidden` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  PRIMARY KEY (`id_a`,`user`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains an `a`'s country ID's - This normally has 300M+ rows
DROP TABLE IF EXISTS `a_country`;
CREATE TABLE `a_country` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `country` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_a`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Sample Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1');
INSERT INTO `a` VALUES ('2');
INSERT INTO `a` VALUES ('3');
INSERT INTO `a` VALUES ('4');
INSERT INTO `a` VALUES ('5');
INSERT INTO `a` VALUES ('6');
INSERT INTO `a` VALUES ('7');
INSERT INTO `a` VALUES ('8');
INSERT INTO `a` VALUES ('9');
INSERT INTO `a` VALUES ('10');

INSERT INTO `a_content` VALUES ('1','aa sadd','aa');
INSERT INTO `a_content` VALUES ('2','aasdadd sads','aa');
INSERT INTO `a_content` VALUES ('3','aa daa','aaf df');
INSERT INTO `a_content` VALUES ('4','aa sad','aadf ');
INSERT INTO `a_content` VALUES ('5','aa v','aaxcv');
INSERT INTO `a_content` VALUES ('6','aa c','aavv');
INSERT INTO `a_content` VALUES ('7','aa df','aaaa');
INSERT INTO `a_content` VALUES ('8','aa df','aa s');
INSERT INTO `a_content` VALUES ('9','aa vvv','a a');
INSERT INTO `a_content` VALUES ('10','ss','aaaa');

INSERT INTO `a_hidden` VALUES ('1','1');
INSERT INTO `a_hidden` VALUES ('3','1');
INSERT INTO `a_hidden` VALUES ('5','1');
INSERT INTO `a_hidden` VALUES ('7','1');
INSERT INTO `a_hidden` VALUES ('9','1');

INSERT INTO `a_country` VALUES ('1',1);
INSERT INTO `a_country` VALUES ('1',2);
INSERT INTO `a_country` VALUES ('2',1);
INSERT INTO `a_country` VALUES ('3',2);
INSERT INTO `a_country` VALUES ('4',4);
INSERT INTO `a_country` VALUES ('5',112);
INSERT INTO `a_country` VALUES ('6',33);
INSERT INTO `a_country` VALUES ('7',44);
INSERT INTO `a_country` VALUES ('8',66);
INSERT INTO `a_country` VALUES ('9',88);
INSERT INTO `a_country` VALUES ('10',99);

Query:

# Extremely slow and takes hours even though it's using indexes all over. If I remove one join it's instantly fast though
SELECT a.id
FROM  `a` 
LEFT JOIN a_hidden ON a_hidden.id_a = a.id && a_hidden.user=1
LEFT JOIN a_content ON a_content.id_a = a.id
INNER JOIN a_country ON a_country.id_a = a.id && a_country.country IN (1,2,3) 
WHERE a_hidden.id_a IS NULL && MATCH (a_content.title, a_content.description) AGAINST ('search' IN BOOLEAN MODE)
ORDER BY a.id DESC 
LIMIT 0 , 50

The Explain of the above query and table structure:

enter image description here

0
  • LEFT JOIN is in the way -- it prevents starting with the MATCH, which would be optimal.
  • is_hidden complicates indexing; either remove such rows from the tables, or filter afterwards.
  • Since a.id is everywhere, and it is the only selected column, this can't be a realistic query.
1
  • Thanks for the response, the other fields that are selected are all from the a table which did not affect performance of this large query by much when I removed it and just used a.id. I kept it removed here for simplicity sakes. is_hidden is mapped to certain users so I can't remove the rows from the table because that would remove it for all users. Is there a better way to do what I am doing apart from having to use a search engine which is what I had to do because I found no other option?
    – JJJ
    Sep 28 '16 at 10:34
0

Well.. MySQL doesn't work well as a search engine. I went with a search engine and selects with multiple filters are instant.

-1

Try putting && a_hidden.user=1 and && a_country.country IN (1,2,3) in WHERE clause. Don't add conditions in join keep conditions in WHERE Clause.

1
  • 1
    && a_hidden.user=1 is part of a non-exists clause. You cannot put that in a WHERE clause without changing the logic of the query. Sep 24 '16 at 17:36
-1

I'm moving from a SQL Server Express to a MySql Database And found some queries with performance bottleneck.

I've managed to it using a pattern "Column Not In (Select ...)" from old Sybase Databases and MySql Works well in some cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.