Mysql optimization question - How to apply AND logic in search and limit on results in one query?
- by sandeepan-nath
This is a little long but I have provided all the database structures and queries so that you can run it immediately and help me.
Run the following queries:-
CREATE TABLE IF NOT EXISTS `Tutor_Details` (
`id_tutor` int(10) NOT NULL auto_increment,
`firstname` varchar(100) NOT NULL default '',
`surname` varchar(155) NOT NULL default '',
PRIMARY KEY (`id_tutor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
INSERT INTO `Tutor_Details` (`id_tutor`,`firstname`, `surname`) VALUES
(1, 'Sandeepan', 'Nath'),
(2, 'Bob', 'Cratchit');
CREATE TABLE IF NOT EXISTS `Classes` (
`id_class` int(10) unsigned NOT NULL auto_increment,
`id_tutor` int(10) unsigned NOT NULL default '0',
`class_name` varchar(255) default NULL,
PRIMARY KEY (`id_class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ;
INSERT INTO `Classes` (`id_class`,`class_name`, `id_tutor`) VALUES
(1, 'My Class', 1),
(2, 'Sandeepan Class', 2);
CREATE TABLE IF NOT EXISTS `Tags` (
`id_tag` int(10) unsigned NOT NULL auto_increment,
`tag` varchar(255) default NULL,
PRIMARY KEY (`id_tag`),
UNIQUE KEY `tag` (`tag`),
KEY `id_tag` (`id_tag`),
KEY `tag_2` (`tag`),
KEY `tag_3` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;
INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
(1, 'Bob'),
(6, 'Class'),
(2, 'Cratchit'),
(4, 'Nath'),
(3, 'Sandeepan'),
(5, 'My');
CREATE TABLE IF NOT EXISTS `Tutors_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0',
`id_tutor` int(10) default NULL,
KEY `Tutors_Tag_Relations` (`id_tag`),
KEY `id_tutor` (`id_tutor`),
KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `Tutors_Tag_Relations` (`id_tag`, `id_tutor`) VALUES
(3, 1),
(4, 1),
(1, 2),
(2, 2);
CREATE TABLE IF NOT EXISTS `Class_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0',
`id_class` int(10) default NULL,
`id_tutor` int(10) NOT NULL,
KEY `Class_Tag_Relations` (`id_tag`),
KEY `id_class` (`id_class`),
KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `Class_Tag_Relations` (`id_tag`, `id_class`, `id_tutor`) VALUES
(5, 1, 1),
(6, 1, 1),
(3, 2, 2),
(6, 2, 2);
Following is about the tables:-
There are tutors who create classes.
Tutor_Details - Stores tutors
Classes - Stores classes created by tutors
And for searching we are using a tags based approach. All the keywords are stored in tags table (while classes/tutors are created) and tag relations are entered in Tutor_Tag_Relations and Class_Tag_Relations tables (for tutors and classes respectively)like this:-
Tags -
id_tag tag (this is a a unique field)
Tutors_Tag_Relations - Stores tag relations while the tutors are created.
Class_Tag_Relations - Stores tag relations while any tutor creates a class
In the present data in database, tutor "Sandeepan Nath" has has created class "My Class" and "Bob Cratchit" has created "Sandeepan Class".
3.Requirement
The requirement is to return tutor records from Tutor_Details table such that all the search terms (AND logic) are present in the union of these two sets - 1. Tutor_Details table 2. classes created by a tutor in Classes table)
Example search and expected results:-
Search Term Result
"Sandeepan Class" Tutor Sandeepan Nath's record from Tutor Details table
"Class" Both the tutors from ...
Most importantly, there should be only one mysql query and a LIMIT applicable on the number of results.
Following is a working query which I have so far written (It just applies OR logic of search key words instead of the desired AND logic).
SELECT td . *
FROM Tutor_Details AS td
LEFT JOIN Tutors_Tag_Relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor
LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor
INNER JOIN Class_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor
LEFT JOIN Tags AS t ON t.id_tag = ttagrels.id_tag
OR t.id_tag = wtagrels.id_tag
WHERE t.tag LIKE '%Sandeepan%'
OR t.tag LIKE '%Nath%'
GROUP BY td.id_tutor
LIMIT 20
Please help me with anything you can. Thanks
