Temat: Przeszukiwanie grup pól
Witam,
Przepraszam że wcześniej nie wrzuciłam tak podstawowych danych które umożliwiły by wam łatwe przetestowanie własnych zapytań oraz porównanie ich z moim pomysłem na rozwiązanie problemu.
Struktura bazy
CREATE TABLE IF NOT EXISTS `pole` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nazwa` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
CREATE TABLE IF NOT EXISTS `subskrybent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(200) NOT NULL,
`imie` varchar(200) NOT NULL,
`nazwisko` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
CREATE TABLE IF NOT EXISTS `subskrybent_grupa` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subskrybent_id` int(11) NOT NULL,
`nazwa` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
KEY `subskrybent_id` (`subskrybent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
CREATE TABLE IF NOT EXISTS `subskrybent_grupa_pole` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subskrybent_grupa_id` int(11) NOT NULL,
`pole_id` int(11) NOT NULL,
`wartosc` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
KEY `subskrybent_grupa_id` (`subskrybent_grupa_id`,`pole_id`),
KEY `pole_id` (`pole_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
INSERT INTO `subskrybent` (`id`, `email`, `imie`, `nazwisko`) VALUES
(1, 'jan.kowalski@gmail.com', 'Jan', 'Kowalski'),
(2, 'franciszek.nowak@gmail.com', 'Franciszek', 'Nowak');
INSERT INTO `pole` (`id`, `nazwa`) VALUES
(1, 'Miejscowość'),
(2, 'Kod pocztowy'),
(3, 'Wiek');
INSERT INTO `subskrybent_grupa` (`id`, `subskrybent_id`, `nazwa`) VALUES
(1, 1, 'Grupa 1'),
(2, 1, 'Grupa 2'),
(3, 1, 'Grupa 3'),
(4, 2, 'Grupa 1'),
(5, 2, 'Grupa 2'),
(6, 2, 'Grupa 3');
INSERT INTO `subskrybent_grupa_pole` (`id`, `subskrybent_grupa_id`, `pole_id`, `wartosc`) VALUES
(1, 1, 1, 'poznań'),
(2, 1, 2, '00-000'),
(3, 2, 1, 'kraków'),
(4, 3, 1, 'poznań'),
(5, 3, 2, '99-999'),
(6, 4, 1, 'łódź'),
(7, 5, 1, 'kraków'),
(8, 5, 3, '29'),
(9, 6, 1, 'poznań'),
(10, 6, 2, '99-999');
ALTER TABLE `subskrybent_grupa`
ADD CONSTRAINT `subskrybent_grupa_ibfk_1` FOREIGN KEY (`subskrybent_id`) REFERENCES `subskrybent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `subskrybent_grupa_pole`
ADD CONSTRAINT `subskrybent_grupa_pole_ibfk_1` FOREIGN KEY (`subskrybent_grupa_id`) REFERENCES `subskrybent_grupa` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `subskrybent_grupa_pole_ibfk_2` FOREIGN KEY (`pole_id`) REFERENCES `pole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Podgląd danych:
SELECT s.imie, s.nazwisko, sg.nazwa grupa, p.nazwa, sgp.wartosc
FROM `subskrybent` s
INNER JOIN subskrybent_grupa sg ON s.id = sg.subskrybent_id
INNER JOIN subskrybent_grupa_pole sgp ON sgp.subskrybent_grupa_id = sg.id
INNER JOIN pole p ON sgp.pole_id = p.id
ORDER BY s.id, sg.nazwa
Wyszukiwanie:
SELECT * FROM (SELECT s.imie, s.nazwisko, sg.nazwa grupa, p.nazwa, sgp.wartosc, count(sgp.subskrybent_grupa_id) AS ilosc_pol
FROM `subskrybent` s
INNER JOIN subskrybent_grupa sg ON s.id = sg.subskrybent_id
INNER JOIN subskrybent_grupa_pole sgp ON sgp.subskrybent_grupa_id = sg.id
INNER JOIN pole p ON sgp.pole_id = p.id
WHERE
(sgp.pole_id = '1' AND sgp.wartosc = 'poznań') OR
(sgp.pole_id = '2' AND sgp.wartosc = '00-000')
group by sgp.subskrybent_grupa_id) AS pola WHERE ilosc_pol = 2
Wyszukiwanie nie wydaje mi się na zbyt optymalne i samej struktury bazy też w stu procentach nie jestem pewien. Wydaje mi się to rozwiązanie dość elastyczne pytanie tylko jak się zachowa ta struktura, gdy załaduje się tam kilka milionów rekordów. Jakieś pomysły na przeprowadzenie testów oraz modyfikacji zapytań i struktury ?
пропозиції роботи
