wyszukiwanie - problem z zapytaniem Opcje

[qchar]

Witam

Mam dużą bazę danych ze zdjęciami, i słowami kluczowymi. W uproszczeniu baza składa się z 2 tabel: zdjęcia z polami id_zdjecia i ścieżką do zdjęcia oraz tabelę słowa kluczowe, gdzie mam pola id, id_zdjecia, slowo. I teraz chciałbym jednym zapytaniem sql znaleźć zdjęcia które mają przypisane np 2 słowa kluczowe np: jeśli wpiszę "piłka nożna", chcę znaleźć tylko te identyfikatory zdjęć które mają słowo PIŁKA i NOŻNA. Wszelkie moje próby kończą się 2 wyjściami. Albo dostaję wynik w postaci identyfikatorów zdjęć zawierających słowo piłka lub słowo nożna, albo pusty zbiór danych. Oczywiście mam w bazie słów kluczowych takie wpisy, gdzie do jednego identyfikatora zdjęcia przypisane są oba te słowa. Robię to mniejwiecej tak:
OPCJA 1:

[SQL] pobierz, plaintext 
SELECT DISTINCT img_id FROM keywords WHERE word LIKE "PILKA%" OR word LIKE "NOZNA%"
[SQL] pobierz, plaintext 

OPCJA 2:

[SQL] pobierz, plaintext 
SELECT DISTINCT img_id FROM keywords WHERE word LIKE "PILKA%" AND word LIKE "NOZNA%"
[SQL] pobierz, plaintext 

Proszę o jakąś podpowiedź, bo stanąłem w miejscu, w którym nie spodziewałem się w ogóle problemu...

[Mchl]

WHERE word LIKE '%PILKA%' OR word LIKE '%NOZNA%'

[qchar]

Nie wiem, może nie byłem za bardzo precyzyjny, ale takie zapytanie zwraca mi tak jak napisałem wszystkie zdjęcia powiązane ze słowem PIŁKA oraz te ze słowem NOŻNA konkretnie rzecz ujmując ~ok 35000 rekordów. A ja chciałbym dostać TYLKO te zdjęcia które opisane są oboma słowami, których w bazie mam mniejwięcej 12000.

[Mchl]

No to w takim razie:

WHERE word LIKE '%PILKA%' AND word LIKE '%NOZNA%'

[qchar]

No dobra, ale jak popatrzysz na mój pierwszy post OBA rozwiązania które proponujesz są w tam i oba zapytania nie działają, przynajmniej nie tak jak bym tego oczekiwał. Dlatego właśnie piszę na forum...

[Mchl]

Ale jak popatrzyzs na moje propozycje, to zauważysz że różnią się od twoich małymi, ale istotnymi detalami (dosłownie dwoma)

[qchar]

Twoja propozycja nie działa, bo jest praktycznie identyczna jak moja. Co do różnic, widzę 2, moim zdaniem nie istotne dla istoty problemu: ja mam cudzysłowy, ty ', ja mam jeden znak procenta po wyrazie który szukam(bo tak własnie chce szukać - to co wpiszę+jakieś znaki, stąd WYRAZ%), Ty proponujesz % także przed wyrazem, mi zupełnie niepotrzebne(bo to oznaczało by szukania fraz jakiesZnakiWYRAZjakiesZnaki). Dodam dla uściślenia że w polu word zawsze znajduje się jedno słowo, więc prawdę mówiąc można by szukać bez procentów, lub w ogóle pisząc word = "SZUKANYWYRAZ". Ale żeby nie było - sprawdziłem mimo wszystko. Więc stwierdzam raz jeszcze - nie działa. W obrazku zrzut ekranu:

Jeśli czegoś więcej nie widzę, proszę o oświecenie, bo mija już drugi dzień jak z tym walczę a rozwiązania łopatologiczne jakie stosuje są niedopuszczalnie wolne.

[Mchl]

No to trzeba było zacząć od opisania struktury danych. Zasugerowałem się Twoimi zapytaniami, że trzymasz wszystkie słowa kluczowe w jednym polu. (W sumie moja wina, bo z Twojego pierwszego posta wynika co innego)

Ok.

Spróbuj tak:

Kod

SELECT fl_kw_img_id AS c, GROUP_CONCAT(fl_kw_word) AS w FROM keywords GROUP BY fl_kw_img_id HAVING w LIKE '%STADION%' AND w LIKE '%WARSZAWA%'

Ten post edytował Mchl 31.12.2009, 08:49:40

[qchar]

No właśnie taką mam strukturę narzuconą, i raczej muszę przy niej pozostać, choć sam pewnie bym to trochę inaczej zorganizował. Cieszę się, że w końcu widzę że można to zrobić jednym zapytaniem. Tylko pozostał mi problem szybkości. Bo pisząc w ten sposób jak proponujesz czekam na wynik 30s. Do tej pory, ponieważ, nie potrafiłem napisać tego jedną linijką (bo spodziewałem się że będzie szybciej) radziłem sobie w taki sposób, że pobierałem wszystkie id zdjęć zawierające jedno słowo, wszystkie id zawierające słowo, i dalej już za pomocą PHP w otrzymanych tablicach wybierałem te zawierające oba. I taki sposób trwa nie uwzględniając cache mysql-a ok 15 sekund. Ale mimo wszystko jestem wdzięczny bardzo bo myślę że mam punkt wyjścia jakiś do dalszych rozważań, szczególnie że niewiele mam tylko kilka takich słów których jest w bazie tak dużo że powodują problem. I w końcu jakieś życiowe zastosowanie funkcji GROUP_CONCAT o której też myślałem. Jeszcze raz dzięki.

[Mchl]

Inny sposób, być może szybszy, może wyglądać tak:

Kod

SELECT t1.fl_kw_img_id AS c FROM keywords AS t1 CROSS JOIN keywords AS t2 USING (fl_kw_img_id) WHERE t1.fl_kw_word = 'STADION' AND t2.fl_kw_word = 'WARSZAWA'

Problem z tym jest taki, że dla dwóch, czy trzech słow to będzie jeszcze jako tako, ale potem coraz bardziej się ślimaczy (bo trzeba kolejne JOINy dorzucać)

[qchar]

O to jest świetne! Wielkie dzięki!

[Mchl]

Dla formalności zapytam: indeksy w tej tabeli masz? Jakie?

[qchar]

Mam, mam. Tak to mniejwięcej wygląda

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `keywords` (
  `fl_kw_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `fl_kw_img_id` bigint(20) UNSIGNED NOT NULL,
  `fl_kw_word` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`fl_kw_id`),
  KEY `fl_kw_img_id` (`fl_kw_img_id`),
  KEY `fl_kw_word` (`fl_kw_word`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
[SQL] pobierz, plaintext 

[Mchl]

Kod

ALTER TABLE keywords
  ADD INDEX imgID_word(fl_kw_img_id,fl_kw_word)

(na dobrą sprawę, to mógłby być Twój PRIMARY)

Sprawdź EXPLAINem, ale wydaje mi się, że powinien dobrze pasować do tego zapytania.

[qchar]

A to nie jest tak, że polo PRIMARY KEY musi być unikalne? Bo u mnie w tej tabeli tak nie jest stąd podejrzewam oddzielne pole z indeksem. Ale nawet w takiej strukturze to ostatnie zapytanie nieźle dawało radę, mimo że wiele czasu na testy nie miałem przed sylwkiem, więc pewnie będe z niego intensywnie korzystał.

[Mchl]

Musi być, ale podejrzewam, że nie masz dwa razy tego samego słowa przypisanego do jednego obrazka.

[qchar]

No właśnie niestety mam, bo ktoś tworząc tą całą bazę poszedł w wielu miejscach na skróty. Dlatego też pewnie w miarę czasu będę szukał sposobu żeby te duplikaty łatwo szybko i przyjemnie usunąć.

[Mchl]

UWAGA: Nie sprawdzone. Nie biorę gwarancji. Nie testować na żywych danych.

To powinno załatwić sprawę duplikatów

Kod

DELETE
  FROM keywords
  WHERE fl_kw_id IN (
    SELECT DISTINCT t1.fl_kw_id
    FROM keywords AS t1
    CROSS JOIN keywords AS t2
    USING(fl_kw_img_id,fl_kw_word)
    WHERE t1.fl_kw_id > t2.fl_kw_id
  )

Ten post edytował Mchl 3.01.2010, 18:47:35

[qchar]

Wywała błąd:
#1093 - You can't specify target table 'keywords' for update in FROM clause

Z braku czasu nie googlowałem za dużo, ale nie wiem czy nie trafiliśmy na jakiś bug w mysql...

[Mchl]

Nie nie... to ma sens...
Spróbuj tego:

Kod

DELETE
  k
  FROM
  keywords AS k,
  (
    SELECT DISTINCT t1.fl_kw_id
    FROM keywords AS t1
    CROSS JOIN keywords AS t2
    USING(fl_kw_img_id,fl_kw_word)
    WHERE t1.fl_kw_id > t2.fl_kw_id
  ) AS sq
  WHERE k.fl_kw_id = sq.fl_kw_id