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...

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

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.

No to w takim razie:

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

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...

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

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.

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:

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.

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


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ć)

O to jest świetne! Wielkie dzięki!

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

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 

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

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

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ł.

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

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ąć.

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

To powinno załatwić sprawę duplikatów

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...

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

Strasznie wolno to działa, więc rezultaty będę znał dopiero rano, jak zostawię to na noc, bo teraz potrzebuję dostępu do bazy, a komenda go blokuje.

Sporo musisz mieć tych duplikatów chyba...

Dokładnych danych nie znam, ale wygląda to mniej więcej tak że najczęstsze słowo kluczowe występuje w bazie ponad 30tys razy, a zdjęć jest tylko ok 26 tysięcy. Może to dać Ci jakieś pojecie ile tam jest "syfu" Wszystkich słów razem jest ok 600tys i to już po odfiltrowaniu wielu rzeczy, bo zaczynałem z pułapu 750tys słów.

I jeszcze jedno pytanie, bo napewno zostaję przy konstrukcji z CROSS JOIN. Gdy chcę 3 słowa: WARSZAWA, STADION, KONFERENCJA to robię tak i działa:

[SQL] pobierz, plaintext 
SELECT DISTINCT t1.fl_kw_img_id AS id
FROM keywords AS t1
CROSS JOIN keywords AS t2
USING ( fl_kw_img_id ) 
CROSS JOIN keywords AS t3
USING ( fl_kw_img_id ) 
WHERE t1.fl_kw_word LIKE 'STADION%'
AND t2.fl_kw_word LIKE 'WARSZAWA%'
AND t3.fl_kw_word LIKE 'KONFERENCJA%'
[SQL] pobierz, plaintext 

Gdybym chciał wszystkie zdjęcia gdzie jest słowo kluczowe WARSZAWA, STADION ale nie ma słowa KONFERENCJA to spłodziłem coś takiego, ale to tez nie chce współpracować i nie uwzględnia 3 warunku:

[SQL] pobierz, plaintext 
SELECT DISTINCT t1.fl_kw_img_id AS id
FROM keywords AS t1
CROSS JOIN keywords AS t2
USING ( fl_kw_img_id ) 
CROSS JOIN keywords AS t3
USING ( fl_kw_img_id ) 
WHERE t1.fl_kw_word LIKE  'STADION%'
AND t2.fl_kw_word LIKE  'WARSZAWA%'
AND t3.fl_kw_word NOT LIKE  'KONFERENCJA%'
[SQL] pobierz, plaintext 

WHY, OH WHY?

a moze tak ?

[SQL] pobierz, plaintext 
 
      SELECT DISTINCT t1.fl_kw_img_id AS id
      FROM keywords AS t1
      CROSS JOIN keywords AS t2
      USING ( fl_kw_img_id )
      CROSS JOIN keywords AS t3
      USING ( fl_kw_img_id )
      WHERE 
      (t1.fl_kw_word LIKE 'STADION%'
      AND t2.fl_kw_word LIKE 'WARSZAWA%')
      AND (t3.fl_kw_word NOT LIKE 'KONFERENCJA%')
 
[SQL] pobierz, plaintext 

Nie, niestety nawiasy nic nie pomagają. Ciągle dostaję wszystkie rekordy, znaczy również te zawierające słowo KONFERENCJA.

Nieubłagane prawa logiki zbiorów.

Musiałbyś zrobić coś takiego:
1. Uzyskać listę rekordów które mają słowa 'STADION' i 'WARSZAWA'
2. Z tej listy usunąć rekordy które mają słowo 'KONFERENCJA'

Krok 1. Potrafisz. Krok 2 właściwie też powinieneś. Kwestia odpowiedniego ułożenia podzapytania.

Co prawda chyba trafiło Ci się parę literówek, ale ostatecznie poprawiony przykład o którym mówimy wygląda tak:

[SQL] pobierz, plaintext 
SELECT sq1.id
FROM (
 
SELECT DISTINCT t1.fl_kw_img_id AS id
FROM keywords AS t1
CROSS JOIN keywords AS t2
USING ( fl_kw_img_id ) 
WHERE t1.fl_kw_word LIKE  'STADION%'
AND t2.fl_kw_word LIKE  'WARSZAWA%'
) AS sq1
LEFT JOIN (
 
SELECT fl_kw_img_id
FROM keywords
WHERE fl_kw_word =  'KONFERENCJA'
) AS sq2 ON (
sq1.id = sq2.fl_kw_img_id
)
WHERE sq2.fl_kw_img_id IS NULL
[SQL] pobierz, plaintext 

I działa też zajefajnie. Dzięki wielkie. Teraz tylko jakoś sprytnie to generować z php ale z tym już dam radę

BTW, mógłbyś polecić jakieś źródła w necie, albo książki żeby się trochę podszkolić w sql, bo widzę że oprócz prostych selectów, i innych takich przydało by się lepiej zgłębić temat.

Najwięcej się nauczysz rozwiązując prawdziwe problemy (własne lub cudze - myślisz że ja tutaj siedzę dla zabawy? )

Ale są też różne strony typu:
http://sql-ex.ru/
to co prawda MSSQL a nie MySQL, ale logika to logika niezależnie od języka.

Pewnie, że najlepiej na przykładach. Choć do tej pory wystarczała mi moja (mizerna) wiedza, bo i problemy jakie miałem przed sobą były trywialne. Ale ta stronka całkiem ciekawa. Musze znaleźć teraz tylko trochę czasu