Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

LEFT JOIN i sortiranje

[es] :: MySQL :: LEFT JOIN i sortiranje

[ Pregleda: 2885 | Odgovora: 18 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon LEFT JOIN i sortiranje05.06.2014. u 19:09 - pre 119 meseci
U bazi imam 4 tabele:

- kategorija
- prikaz
- slika
- slika_kategorija

Jedna slika moze da ima vise kategorija, a u tabeli prikaz cuvam podatke o broju prikaza slike za odredjenu kategoriju + sajt posto se slika prikazuje na vise sajtova i sve to treba da se sortira po broju prikaza.

Ono sto pokusavam da uradim jeste da napravim upit koji ce mi izvuci sve slike za odredjeni sajt i odredjenu kategoriju sortirane po broju prikaza ali u cemu je problem.

Ako je tabela prikaz prazna i nad njoj radim insert/update samo kada neka slika ima 1 ili vise prikaza, onda imam problem sa LEFT JOIN-om jer treba da sortiram podatke po broju prikaza za odredjeni sajt i odredjenu kategoriju, a nemam dovoljno podataka u toj tabeli i onda upit pravi tmp tabele i sve radi jako sporo.

Ako pak napunim tabelu prikaz pri cemu unesem podatke za svaku sliku i kategoriju i stavim da je broj prikaza 0, onda to sve radi kako treba, mada mi se taj pristup ne svidja jer ako baza ima 1M slika i neka u proseku one imaju 3 kategorije to je 3M rekorda i ako napravim 10 sajtova, to znaci da bi tabela morala da ima 30M rekorda.

Da li postoji resenje za ovo u koliko slika nema ni jedan prikaz da ne moram da cuvam vrednost 0, a da ipak mogu da sortiram po broju prikaza i da te slike budu poslednje prikazane?

Drugi je problem sajt_id koji se nalazi samo u tabeli prikaz, pa ako zelim sve slike za odredjeni sajt, a one trenutno imaju 0 prikaza (tabela je prazna), necu dobiti ni jedan rezultat jer na upit WHERE sajt_id = 1, MySQL mi nece vratiti nista.

Ispod je sql dump tabela.

Hvala na pomoci!

Code:
-- phpMyAdmin SQL Dump
-- version 4.0.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 05, 2014 at 05:59 PM
-- Server version: 5.6.12-log
-- PHP Version: 5.4.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `test`
--
CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `test`;

-- --------------------------------------------------------

--
-- Table structure for table `kategorija`
--

CREATE TABLE IF NOT EXISTS `kategorija` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `naziv` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `kategorija`
--

INSERT INTO `kategorija` (`id`, `naziv`) VALUES
(1, 'Priroda'),
(2, 'Automobili'),
(3, 'Zelenilo');

-- --------------------------------------------------------

--
-- Table structure for table `prikaz`
--

CREATE TABLE IF NOT EXISTS `prikaz` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sajt_id` smallint(5) unsigned NOT NULL,
  `slika_id` int(10) unsigned NOT NULL,
  `kategorija_id` int(10) unsigned NOT NULL,
  `broj_prikaza` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `slika`
--

CREATE TABLE IF NOT EXISTS `slika` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `naziv` varchar(64) NOT NULL,
  `url` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `slika`
--

INSERT INTO `slika` (`id`, `naziv`, `url`) VALUES
(1, 'Drvo', '/slike/drvo.jpg'),
(2, 'BMW', '/slike/bmw.jpg');

-- --------------------------------------------------------

--
-- Table structure for table `slika_kategorija`
--

CREATE TABLE IF NOT EXISTS `slika_kategorija` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `slika_id` int(10) unsigned NOT NULL,
  `kategorija_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `slika_kategorija`
--

INSERT INTO `slika_kategorija` (`id`, `slika_id`, `kategorija_id`) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 1, 3);

Pozdrav, NenadS!
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 08:35 - pre 119 meseci
Ne valja ti dizajn baze.

Koje slike pripadaju sajtu 12345? Da li uopšte postoji taj sajt?

Kom sajtu pripada slika BMW?

Fali ti šifarnik sajtova i veza između slike i sajta...
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 09:33 - pre 119 meseci
Da, to mi je jasno ali posto sve slike pripadaju svim sajtovima, a jedina razlika je u nacinu sortiranja, mislio sam da bi nekako mogao da izbegnem potrebu za tom tabelom ili tim tabelama koja bi cuvala info koja slika pripada kom sajtu?
Pozdrav, NenadS!
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 09:52 - pre 119 meseci
Citat:
sve slike pripadaju svim sajtovima


Ovo je novi podatak, koji pre nisi naveo.
Zatim, u tabeli kategorija id ti je tipa mediumint(8) a u tabeli prikaz kategorija_id je int(10). Izgleda kao da si na dizajnu baze potrošio tačno onoliko vremena koliko je trebalo da nabrojiš kolone, bez nekog preteranog razmišljanja.
Pitanje je i da li ti treba kategorija_id u tabeli prikaz, uzimajući u obzir količinu truda koji si uložio u dizajn.
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 10:06 - pre 119 meseci
U pravu si, mada je to test baza sa kojom zelim da nadjem najbolje resenje za problem na koji sam naisao jer tu svakako nedostaju i indeksi.

To polje jeste obavezno jer bez njega ako racunam samo broj prikaza slike, necu znati na koju se kategoriju odnosi, pa ako slika ima 100 prikaza u kategoriji 1, ne znaci da mora da ima 100 prikaza u kategoriji 2 i ako se nalazi u obe kategorije.

Jos uvek nisam nasao zadovoljavajuce resenje osim da napunim tabelu i da broj prikaza bude 0 i onda da ga povecavam kada neko klikne na sliku...
Pozdrav, NenadS!
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 10:14 - pre 119 meseci
Evo jednog upita:

Code (sql):

SELECT p.sajt_id, idk, kat, ids, sl, p.broj_prikaza
  FROM (SELECT k.naziv kat, s.naziv sl, k.id idk, s.id ids
          FROM xxx_kategorija k, xxx_slika s, xxx_slika_kategorija sk
         WHERE k.id = sk.kategorija_id
           AND s.id = sk.slika_id) x
  LEFT JOIN xxx_prikaz p
    ON p.slika_id = x.ids
   AND p.kategorija_id = x.idk
 


Međutim ovo ne valja po više osnova:
Šta ako ima kategorija koje nemaju ni jednu sliku?
Šta ako postoje slike koje nisu ni u jednoj kategoriji?
Šta ako je jedna slika stavljena više puta u istu kategoriju?

[Ovu poruku je menjao djoka_l dana 06.06.2014. u 12:11 GMT+1]
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 11:33 - pre 119 meseci
Zašto tabela slika_kategorija ima id?
Ako ima id, zašto u tabeli prikaz ne stoji taj id umesto kobinacije slika_id, kategoraija_id?
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 11:38 - pre 119 meseci
U pravu si, ovo nema bas nekog smisla... ali to je ispalo tako kako jeste jer su se stvari menjale u hodu bez mnogo razmisljanja o dobrom resenju.

Nista, nazad u labaratoriju posto sada znam sta je potrebno, samo treba doci do najboljeg resenja.

Ako imas neki predlog kako bi ti to resio, bio bih ti zahvalan da ga podelis sa nama, a ako ne, hvala ti sto si mi ukazao na probleme u dizajnu baze :)
Pozdrav, NenadS!
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 11:41 - pre 119 meseci
Pa stavio sam ti upit iz kojeg možeš da izvučeš sve što ti treba. Samo dodaj svoje where uslove na kraj...
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 11:49 - pre 119 meseci
Da, video sam ali to je sa ovim mojim dizajnom koji nije optimalan i plasim se da ce raditi sporo pri malo vecoj poseti ali testiracu ga pa cemo videti.

Hvala jos jednom! :)
Pozdrav, NenadS!
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 15:46 - pre 119 meseci
Cini mi se da mi je skroz pogresan pristup za resavanje ovog problema.

Upit treba da mi vrati rezultate i kada je tabela prikaz prazna ali ako ima nekih podataka, onda treba da je koristi i da sortira slike prema podacima koje ima i to za odredjeni sajt.

Bas sam se zapetljao sa ovim, mada u ovom trenutku ne mogu da pronadjem elegantno i efikasno resenje za ovaj problem :(
Pozdrav, NenadS!
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 19:26 - pre 119 meseci
Ti svakako možeš da radiš RIGHT OUTER JOIN sa 'prikaz' na 'slika' i da
broj prikaza vadiš kao ISNULL(broj_prikaza, 0) tako da dobiješ to što tebi treba,
čak i ako slika nema rekord u 'prikaz' tabeli za ciljani sajt i kategoriju.

Međutim, cenim da bi ti upit brže radio i da imaš svih 30M rekorda u 'prikaz' tabeli sa
sve nulama za ove koje nisu nikada prikazane.
Ne razumem zbog čega bežiš od broja rekorda kada brzina izvršavanja upita
daleko više zavisi od strukture i strategije izvršenja a daleko manje od broja rekorda.

Dakle, ja bih stavio i nule u 'prikaz' tabeli, indeksirao polja 'sajt_id',
'kategorija_id' i 'broj_prikaza' (DESC pošto valjda tako sortiraš u upitu)
i cenim da bi upit radio mnogo brže nego bilo kakva varijanta
RIGHT OUTER JOIN-a u kombinaciji sa ISNULL (po kojoj se na kraju i sortira).
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.com
Via: [es] mailing liste

Sajt: mysql.rs


+2377 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 19:47 - pre 119 meseci
mnogo gresaka ovde .. sad, pitanje je sledece, da li ti moras da radis
sa ovom postavkom (ova struktura) ili si jos uvek u fazi razvoja, ako si
u fazi razvoja aj batali "left join" i napisi lepo sta je projektni
zadatak - sta je cilj, sta hoces da postignes, pa da vidimo kako da ti
pomognemo da dodjes do resenja .. sintaksa sql-a ovde nije resenje za
tvoj problem
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 20:07 - pre 119 meseci
@bogdan,

Nema neke mudrosti, rekao je čovek da mu treba da izvuče
sve slike za određeni sajt i kategoriju sortirano po broju pregleda.
Taj upit često izvršava i treba da bude brz.

Struktura tabele 'pregled' definitivno paše i ne vidim da može da
bude bolja za rešavanje ovog specifičnog problema nego što već jeste.
Eventualno može da uradi particionisanje 'pregled' tabele po
'sajt_id' i/ili 'kategorija_id' kolonama radi dodatnih performansi
ako MySql to podržava.

E sad, ako je logika "promašena" i njemu zapravo i ne treba
ovo što pokušava da uradi, to je već drugi problem - problem dizajna aplikacije
i nema smisla da ga ovde rešavamo Sql sintaksom, itd...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.com
Via: [es] mailing liste

Sajt: mysql.rs


+2377 Profil

icon Re: LEFT JOIN i sortiranje06.06.2014. u 22:12 - pre 119 meseci
zato i pitam, koliko ja procitah on nije siguran sta mu tacno treba, pa
reko bolje da napise sta mu tacno treba bez toga kako je on to mislio da
uradi .. obicno to pomogne njemu samom da dodje do pravog resenja
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje07.06.2014. u 09:49 - pre 119 meseci
Hvala vam svima na tome sto ste se ukljucili da mi pomognete, to mi puno znaci :)

Evo sta zelim da postignem.

Imam tabelu koja civa podatke o svim slikama, tabelu koja cuva podacke o kategorijama, zatim tabelu gde povezujem slike sa odredjenim kategorijama pri cemu jedna slika pripada najmanje jednoj kategoriji, a moze ih imati i vise ali se ne ponavljaju u smislu da jedna slika ima dve iste kategorije.

Ono sto zelim da postignem jeste da na vise sajtova prikazujem te iste slike/kategorije ali da se slike sortiraju po broju prikaza u zavisnosti od sajta i kategorije u kojoj se nalaze.

Ako jedna slika ima dve kategorije, zelim da brojim broj prikaza za svaku kategoriju posebno i svaki sajt posebno i da ih tako prikazujem u opadajucem rasporedu po broju prikaza.

Ja sada trenutno koristim tabelu koju sam generisao i koja bukvalno ima svaku kat/slika kombinaciju gde je broj pregleda 0 i to radi brzo bez temp tabela i ostalih stvari kao sto su filesort, znaci korisri indekse i upiti su brzi ali ta ta tabela koja trenutno ima nesto manje od 3M rekorda zauzima 200+ Mb gde veci deo zauzimaju indeksi ali to bih delimicno mogao da smanjim tako sto bih izbacio kombinaciju slika_id, kategorija_id i ubacio samo id iz tabele slika_kategorija jer ona vec ima te podatke.

E sada, jedan sajt i 200Mb nije strasno ali 10 ili 20 sajtova vec cini bazu velikom pa sam mislio da nekako izbegnem da mi tabele cuvaju broj prikaza ako ih nema i zato sam probao sa LEFT JOIN ali onda imam temp tabele i upiti su znatno sporiji.

Dizajn tabele moze da se promeni ako imate bolje resenje kako bih mogao da resim gore navedeni problem.

Hvala jos jednom svima koji su se ukljucili u diskusiju!
Pozdrav, NenadS!
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: LEFT JOIN i sortiranje07.06.2014. u 10:25 - pre 119 meseci
Kakva strategija prikaza treba da bude, sigurno ne prikazuješ svih milion na stranici?
Da li imaš limit, paging ili nešto treće - ovo može da bude bitan detalj.

Da li su ti slike fizički na serveru i koje su veličine ili ih vučeš sa drugih sajtova?
Ovo pitam iz razloga što ti rekordi u prikaz tabeli za jednu sliku uzimaju oko 500b
a cenim da su ti slike bar 20 puta veće i samim time ušteda na tabeli gubi smisao.
 
Odgovor na temu

NenadS
Nenad Strainovic
Beograd

Član broj: 857
Poruke: 910
*.dynamic.sbb.rs.

Sajt: www.strainovic.com


+7 Profil

icon Re: LEFT JOIN i sortiranje07.06.2014. u 11:29 - pre 119 meseci
Na prvoj strani ce biti prikazane kategorije i kada se udje u neku kategoriju, prikazuju se slike iz te kategorije sortirane po broju prikaza uz paginaciju ali je paginacija ogranicena na prvih 100 strana zbog sporijeg sortiranja uz LIMIT x, y;

Slike su lokalno na serveru i prosecne velicine su 150Kb.

Nije problem storage, vec me brine RAM koji moze biti problem ako su tabele velike, a (mozda) ne postoji realna potreba za tim.

Hvala!
Pozdrav, NenadS!
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: LEFT JOIN i sortiranje07.06.2014. u 11:49 - pre 119 meseci
Pa to bar nije teško proveriti u praksi.

Code:

SELECT slika.*
FROM (SELECT * FROM prikaz WHERE ... ORDER BY ... LIMIT ... OFFSET ...) AS X
INNER JOIN slika ON ...
 
Odgovor na temu

[es] :: MySQL :: LEFT JOIN i sortiranje

[ Pregleda: 2885 | Odgovora: 18 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.