CREATE VIEW - czy to jest optymalne?

0

Witam. Mam taką tabelę

id data
4444 firstdata
4444 second
4444 costamcostam
3333 wsiump
3333 tralala
4444 firstdata

No i sobie ją pobieram.
SELECT * FROM TABLE.
Następnie w PHP przetwarzam sobie tę dane w PHP - grupuję dane poprzez ID.

Lecz problem następuje gdy chcę zrobić pager na stronię, czyli wyświetlać od 0 do 30 rekordów.
Czyli tak jakby użyć zapytania

SELECT * FROM table WHERE ID IN ( SELECT id FROM table GROUP BY id LIMIT 0, 30);

I myślę że w moim przypadku to będzie nie optymalne - moje zapytanie jest zbyt szeczgółowe ( http://4programmers.net/Pastebin/1373 ), to tak jaby 2 te same zapytanie wykonać - myślę że MySQL nie wyrobi.

Dlatego myślę o alternatywie.
Myślę by zrobić
CREATE VIEW tmpview AS SELECT * FROM TABLE;

Następnie by pobrać dane
SELECT * FROM tmpview WHERE id IN (SELECT id FROM tmpview GROUP BY id LIMIT 0,30)

Czy to jest dobre i optymalne rozwiązanie? Czy create view jest dłuższe od podwójnego zapytania o ten sam szczegół?

1
  1. można wiedzieć po co te dwa podzapytania i in? - wyciągnij z nich warunki "do góry"
  2. jak zrobisz widok to to nie przyśpieszy/zwolni zapytania, jedynie je uprości
0
SELECT * FROM table  /* tutaj wybieram rekordy, Nie moge do tego dać zapytania GROUP BY id bo usunie dane, gdzie kolumna data ma inną wartość */

WHERE ID IN ( SELECT id FROM table GROUP BY id LIMIT 0, 30); /* tutaj już grupuję przez ID, po to by pobrać ile jest faktycznych wyników, gdzie jest unikalne ID. Następnie daje LIMIT 0, 30)*/

Man nadzieje że teraz rozumiesz. Jeśli nie o to chodziło, to podaj przykład zapytania/ pseudo zapytanie ... będę wdzięczny.

0

Po co robisz podzapytanie? Po co robisz grupowanie? Po co widok z gwiazdką z jednej tabeli? Dlaczego LIMIT nie dasz od razu po swoim zapytaniu?

Widok służy do wyświetlania tylko wybranych kolumn zazwyczaj z kilku tabel, bez konieczności ich wybierania po poleceniu SELECT. Czyli innymi słowy ma zadanie ukryć szczegółową implementację, która może być skomplikowana. Widok nic nie przyspiesza. Zapytanie wykonane na widoku niczym nie różni się od wykonania na tabeli... z wyjątkiem tego, że na widoku wykona się nieco wolniej.

/edit: w tabeli nie masz unikalnych id? Jeżeli nie chcesz żeby Ci czegoś usunęło w tym group by to dodaj te pole do group by, wtedy nie będziesz musiał robić podzapytania.

0
AdamPL napisał(a)

Po co robisz podzapytanie? Po co robisz grupowanie? Po co widok z gwiazdką z jednej tabeli? Dlaczego LIMIT nie dasz od razu po swoim zapytaniu?

Widok służy do wyświetlania tylko wybranych kolumn zazwyczaj z kilku tabel, bez konieczności ich wybierania po poleceniu SELECT. Czyli innymi słowy ma zadanie ukryć szczegółową implementację, która może być skomplikowana. Widok nic nie przyspiesza. Zapytanie wykonane na widoku niczym nie różni się od wykonania na tabeli... z wyjątkiem tego, że na widoku wykona się nieco wolniej.

O to serwer testowy:
phpmyadmin: http://phpmyadmin.bordeux.net/
user: bordeux_test
pass: test

Proszę wykonać zapytanie:

SELECT 
	* 
FROM  
	`auctions`  
LEFT JOIN 
	`fields_values`
ON 
	`fields_values_auction` = `auctions_id`
LEFT JOIN 
	`fields`
ON 
	`fields_id` = `fields_values_ids`
WHERE 
	`auctions_category`
		LIKE 
	"/%"
AND
	`auctions_id` 
		IN 
			(	
				SELECT 
					`fields_values_auction`
				FROM 
					`fields_values`
				LEFT JOIN 
					`fields`
				ON
					`fields_values_ids` = `fields_id`
				WHERE
					`fields_name` = "cost"
				AND
					`fields_values_val` > 2000	
			)
AND
	`auctions_id` 
		IN 
			(	
				SELECT 
					`fields_values_auction`
				FROM 
					`fields_values`
				LEFT JOIN 
					`fields`
				ON
					`fields_values_ids` = `fields_id`
				WHERE
					`fields_name` = "miasto"
				AND
					`fields_values_val` = "katowice"
			)

I muszę zrobić do tego LIMIT, by zrobić pagger na stronie. auction_id to jest id aukcji... a może ich być nawet milion.

0

Pola, które pobierasz w SELECT wstaw do group by i wtedy zrób limit już bez podzapytania i sprawdź co się szybciej wykona.

Pogrupowanie po tych polach może trwać dłużej niż wykonanie podzapytania, więc musisz to sprawdzić.

0

Nie za bardzo rozumiem, o to chodziło?

 
SELECT 
        *
FROM  
        `auctions`  
LEFT JOIN 
        `fields_values`
ON 
        `fields_values_auction` = `auctions_id`
LEFT JOIN 
        `fields`
ON 
        `fields_id` = `fields_values_ids`
WHERE 
        `auctions_category`
                LIKE 
        "/%"
AND
        `auctions_id` 
                IN 
                        (        
                                SELECT 
                                        `fields_values_auction`
                                FROM 
                                        `fields_values`
                                LEFT JOIN 
                                        `fields`
                                ON
                                        `fields_values_ids` = `fields_id`
                                WHERE
                                        `fields_name` = "cost"
                                AND
                                        `fields_values_val` > 2000        
                        )
AND
        `auctions_id` 
                IN 
                        (        
                                SELECT 
                                        `fields_values_auction`
                                FROM 
                                        `fields_values`
                                LEFT JOIN 
                                        `fields`
                                ON
                                        `fields_values_ids` = `fields_id`
                                WHERE
                                        `fields_name` = "miasto"
                                AND
                                        `fields_values_val` = "katowice"
                        )
	GROUP BY auctions_id, auctions_category, auctions_views,	fields_values_id, fields_values_ids, fields_values_val,	fields_values_auction, fields_id,	fields_category,	fields_name,	fields_search,	fields_search_options, fields_type,	fields_priority
LIMIT 0, 2;

Jeśli tak, to pokazują się 2 rekordy - a aukcja ma więcej niż 2 pola.

1

bez in

SELECT 
  *
FROM  
  auctions a
LEFT JOIN fields_values v ON v.fields_values_auction = a.auctions_id
LEFT JOIN fields f ON f.fields_id = v.fields_values_ids
JOIN (
  SELECT 
    fields_values_auction
  FROM 
    fields_values v 
  LEFT JOIN fields f ON f.fields_id = v.fields_values_ids
  WHERE 
    (f.fields_name = "cost" AND v.fields_values_val > 2000)
    OR (f.fields_name = "miasto" AND v.fields_values_val = "katowice")
  group by 
    fields_values_auction
  having 
    count(fields_values_auction) = 2) x on x.fields_values_auction = a.auctions_id
WHERE 
  auctions_category LIKE "/%"

albo zamiast dwóch in możesz mieć jedno (to jest tylko podzapytanie, reszta jak w Twoim zapytaniu

SELECT 
  fields_values_auction
FROM  
 fields_values v 
LEFT JOIN fields f ON f.fields_id = v.fields_values_ids
WHERE 
  (f.fields_name = "cost" AND v.fields_values_val > 2000)
  OR (f.fields_name = "miasto" AND v.fields_values_val = "katowice")
group by 
  fields_values_auction
having 
  count(fields_values_auction) = 2

Oczywiście jeśli będziesz miał 3 warunki to having musi być = 3

Który wybrać - musisz sprawdzić który będzie szybszy.

I taka subiektywna uwaga ode mnie - używaj aliasów dla tabel i poprzedzaj nimi nazwę pola. Wiem, że Ty pewnie znasz tą bazę na pamięć ale jak ktoś nie wie gdzie jakie pole jest to mu trudno się poruszać w zapytaniu

0

Podziękuj pięknie Miśkowi (piwo wskazane), bo odwalił za Ciebie kawał roboty. Mnie te formatowanie odrzuciło od jakichkolwiek prób przeczytania ze zrozumieniem.

0

Wielkie podziękowania Misiekd! Już n'ty raz mi pomogłeś w trudnych dla mnie zapytaniach. Jeszcze raz, wielkie podziękowanie!

1

tak jak pisałem wcześniej - zrób najpierw testy i zobacz jak wygląda execution plan bo może się okazać, że jednak Twoje zapytanie jest lepsze. Sprawdź dla małej ilości kryteriów i dla większej.
BTW czy jest coś co pokaże execution plan mysqla jakoś normalnie :p

0
Misiekd napisał(a)

tak jak pisałem wcześniej - zrób najpierw testy i zobacz jak wygląda execution plan bo może się okazać, że jednak Twoje zapytanie jest lepsze. Sprawdź dla małej ilości kryteriów i dla większej.
BTW czy jest coś co pokaże execution plan mysqla jakoś normalnie :p

Stanowczo moje zapytanie jest mało wydajne, zrobiłem profilowanie, i wynika iż średni czas wykonania to 0.2351s., z czego jest niezliczona ilość takich operacji:

starting	0.000088
checking permissions	0.000004
checking permissions	0.000002
checking permissions	0.000002
checking permissions	0.000002
checking permissions	0.000002
checking permissions	0.000002
checking permissions	0.000004
Opening tables	0.000046
System lock	0.000011
init	0.000056
optimizing	0.000013
statistics	0.000084
preparing	0.000042
executing	0.000003
Sending data	0.000053
optimizing	0.000012
statistics	0.000027
preparing	0.000018
executing	0.000003

// I TO SIE CIAGLE POWTARZA
Sending data	0.000053
optimizing	0.000012
statistics	0.000027
preparing	0.000018
executing	0.000003
// END

Tak duża, że strona ma 371tyś pixeli.

Natomiast twoje rozwiązanie to poniżej połowy tego czasu 0.09sek:

198241026201171233pm1.png

Jeszcze raz wielkie podziękowanie.

0

Już myślałem że to koniec tematu, ale nie wiem jak obliczyć ile zostało wybranych rekordów.
Mam zapytanie

SELECT 
  *
FROM  
  auctions a
LEFT JOIN fields_values v ON v.fields_values_auction = a.auctions_id
LEFT JOIN fields f ON f.fields_id = v.fields_values_ids
JOIN (
  SELECT 
    fields_values_auction
  FROM 
    fields_values v 
  LEFT JOIN fields f ON f.fields_id = v.fields_values_ids
  WHERE 
    (f.fields_name = "cost" AND v.fields_values_val > 2000)
  GROUP BY 
    fields_values_auction
  HAVING 
    COUNT(fields_values_auction) = 1
LIMIT 0, 2
) x ON x.fields_values_auction = a.auctions_id
WHERE 
  auctions_category LIKE "/%"

Jak pobrać liczbę rekordów, które zostały wybrane? - bo jak widać, robię limit danych do 2.

Dane do serwera testowego:
phpmyadmin: http://phpmyadmin.bordeux.net/
user: bordeux_test
pass: test

Próbowałem dodać SQL_CALC_FOUND_ROWS lecz wynikiem tego zapytania jest:
#1234 - Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'
Natomiast dodanie do 1 i 2 select opcje COUNT(*) lub COUNT( DISTINCT fields_values_auction) daje fałszywe wyniki.

1
  1. robienie limit bez order by to proszenie się o kłopoty (no chyba, że jest to zamierzony efekt)
  2. to mi zwraca jako wynik 18 - tyle samo co rekordów Twoje zapytanie
SELECT 
  count(*) 
FROM  
  auctions a
LEFT JOIN fields_values v ON v.fields_values_auction = a.auctions_id
LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
JOIN (
  SELECT 
    fields_values_auction
  FROM 
    fields_values v 
  LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
  WHERE 
    (f.fields_name = "cost" AND v.fields_values_val > 2000)
  GROUP BY 
    fields_values_auction
  HAVING 
    COUNT(fields_values_auction) = 1 
LIMIT 0, 2 
) x ON x.fields_values_auction = a.auctions_id
WHERE 
  auctions_category LIKE "/%"
0
SELECT 
  COUNT(*) 
FROM  
  auctions a
LEFT JOIN fields_values v ON v.fields_values_auction = a.auctions_id
LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
JOIN (
  SELECT 
    fields_values_auction
  FROM 
    fields_values v 
  LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
  WHERE 
    (f.fields_name = "cost" AND v.fields_values_val > 2000)
  GROUP BY 
    fields_values_auction
  HAVING 
    COUNT(fields_values_auction) = 1 
LIMIT 0, 2 
) x ON x.fields_values_auction = a.auctions_id
WHERE 
  auctions_category LIKE "/%"

Otóż muszę zrobić pager na stronie - do tego potrzebuje ilość wszystkich rekordów, oraz potrzebuje LIMIT, by pobierać częściowo dane. Z limitem jest ok, pobiera tylko 2 rekordy. Lecz jak pobrać ilość wszystkich wyszukanych rekordów?
Liczba 18 jest to liczba ilości pól 2 aukcji (bo taki był limit). Jak zmienisz LIMIT na 0,3 to wynikiem będzie 27 i tak dalej...

Prawidłową liczbą powinna być liczba 4614 .

Tutaj jest pokazany przykład, jak się powinno obliczać ilość wszystkich wylosowanych aukcji:

SELECT 
  COUNT(DISTINCT a.auctions_id) 
FROM  
  auctions a
LEFT JOIN fields_values v ON v.fields_values_auction = a.auctions_id
LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
JOIN (
  SELECT 
    fields_values_auction
  FROM 
    fields_values v 
  LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
  WHERE 
    (f.fields_name = "cost" AND v.fields_values_val > 2000)
  GROUP BY 
    fields_values_auction
  HAVING 
    COUNT(fields_values_auction) = 1 
) x ON x.fields_values_auction = a.auctions_id
WHERE 
  auctions_category LIKE "/%"

Chodzi o scalenie tego zapytania do 1 , by nie pytać serwer 2x o to samo i obciążać go.

1

ale count musisz zrobić tylko na tej części

select
count(*)
from
 (SELECT 
    fields_values_auction
  FROM 
    fields_values v 
  LEFT JOIN `fields` f ON f.fields_id = v.fields_values_ids
  WHERE 
    (f.fields_name = "cost" AND v.fields_values_val > 2000)
  GROUP BY 
    fields_values_auction
  HAVING 
    COUNT(fields_values_auction) = 1) x

a nie na całości

Co do SQL_CALC_FOUND_ROWS to z tego co wyczytałem musi być na tym samym "poziomie" co limit aby to działało
I jeszcze o SQL_CALC_FOUND_ROWS http://mariusz.turek.salon404.pl/post/4,mysql-sql-calc-found-rows-kontra-count

0

Faktycznie. Dzięki wielkie za włożoną chęć w celu pomocy mi.

1 użytkowników online, w tym zalogowanych: 0, gości: 1