First run 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 teachers from the Tutors_Tag_Relations table who have a link 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
Help me modify this query so that it returns all the teachers from the Tutors_Tag_Relations table that refer to both the terms "key1" and "key2" (AND logic instead of OR logic). Please suggest an optimized query, taking into account the huge number of data records (the query must NOT individually extract two sets of instructors corresponding to each keyword, and then find the intersection).
Update
. : -
=============================================== ====================================
, learning_packs_tag_relations (id_tag int (10) unsigned NOT NULL DEFAULT '0', id_tutor int (10) DEFAULT NULL, id_lp int (10) unsigned DEFAULT NULL, Learning_Packs_Tag_Relations_FKIndex1 (id_tag), id_lp (id_lp), id_tag (id_tag)
) ENGINE = InnoDB DEFAULT CHARSET = latin1;
, learning_packs (id_lp int (10) unsigned NOT NULL AUTO_INCREMENT, id_status int (10) unsigned NOT NULL DEFAULT '2', id_author int (10) unsigned NOT NULL DEFAULT '0', name varchar (255) NOT NULL DEFAULT '', (id_lp)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 21;
, tutors_tag_relations (id_tag int (10) unsigned NOT NULL DEFAULT '0', id_tutor int (10) DEFAULT NULL,
tutors_tag_relations (id_tag), id_tutor (id_tutor), id_tag (id_tag)
) ENGINE = InnoDB DEFAULT CHARSET = latin1;
, users (id_user int (10) unsigned NOT NULL AUTO_INCREMENT, name varchar (100) NOT NULL DEFAULT '', surname varchar (155) NOT NULL DEFAULT '',
(id_user)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 52;
, tutor_details (id_tutor int (10) NOT NULL AUTO_INCREMENT, id_user int (10) NOT NULL, (id_tutor)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 60;
, tags (id_tag int (10) unsigned NOT NULL AUTO_INCREMENT, tag varchar (255) DEFAULT NULL, (id_tag), tag (tag)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 5;
ALTER TABLE learning_packs_tag_relationsADD CONSTRAINT Learning_Packs_Tag_Relations_ibfk_1 (id_tag) tags (id_tag) . ;
ALTER TABLE learning_packs
ADD CONSTRAINT Learning_Packs_ibfk_2 (id_author) users (id_user) . ;
ALTER TABLE tutors_tag_relationsADD CONSTRAINT Tutors_Tag_Relations_ibfk_1 (id_tag) tags (id_tag) . ;
INSERT INTO test. users (
id_user,
name,
surname
)
(
NULL, 'Vivian', 'Richards'
), (
NULL, 'Sachin', 'Tendulkar'
);
INSERT INTO test. users (
id_user,
name,
surname
)
(
NULL, 'Don', 'Bradman'
);
INSERT INTO test. tutor_details (
id_tutor,
id_user
)
(
NULL, '52'
), (
NULL, '53'
);
INSERT INTO test. tutor_details (
id_tutor,
id_user
)
(
NULL, '54'
);
INSERT INTO test. tags (
id_tag,
tag
)
(
1, ''
), (
2, ""
);
INSERT INTO test. tags (id_tag, tag) (3, 'Sachin'), (4, 'Tendulkar');
INSERT INTO test. tags (id_tag, tag) (5, "" ), (6, "" );
INSERT INTO test. learning_packs (id_lp, id_status, id_author, name) ('1', '1', '52', 'Cricket 1'), ( '2', '2', '52', 'Cricket 2');
INSERT INTO test. tags (id_tag, tag) ('7', 'Cricket'), ('8', '1');
INSERT INTO test. tags (id_tag, tag) VALUES ('9', '2');
INSERT INTO test. learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '1'), ('8', '52', ' 1' );
INSERT INTO test. learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '2'), ('9', '52', '2');
=============================================== ====================================
-
- - , ( tutor_details), learning_packs, learning_packs_tag_relations
- - , tutors_tag_relations, , learning_packs_tag_relations.
learning_packs . , , ( , , ).
=============================================== ===================================
lp. *
Learning_Packs AS lp
LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp
LEFT JOIN Tutors_Tag_Relations ttagrels ON lp.id_author = ttagrels.id_tutor
LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor
LEFT JOIN u td.id_user = u.id_user
t on (t.id_tag = lptagrels.id_tag) (t.id_tag = ttagrels.id_tag)
lp.id_status = 1 AND (t.tag LIKE "% Vivian%" t.tag LIKE "% Richards%" )
group by lp.id_lp HAVING count (lp.id_lp) > 1 0,20
, "Cricket 1" , Vivian Richards .
,