First execute these tables and data dumps :-
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, 'key1'),
(2, 'key2');
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
(1, 1),
(2, 1);
The following query finds all the tutors from Tutors_Tag_Relations table which have reference to at least one of the terms "key1" or "key2".
SELECT td . *
FROM Tutors_Tag_Relations AS td
INNER JOIN Tags AS t ON t.id_tag = td.id_tag
WHERE t.tag LIKE "%key1%"
OR t.tag LIKE "%key2%"
Group by td.id_tutor
LIMIT 10
Please help me modify this query so that it returns all the tutors from Tutors_Tag_Relations table which have reference to both the terms "key1" and "key2" (AND logic instead of OR logic). Please suggest an optimized query considering huge number of data records (the query should NOT individually fetch two sets of tutors matching each keyword and then find the intersection).
