Keskustelu: Nettisivujen teko: MySQL optimointikysymys

Teen huuto.netin tyyppistä sovellusta Ecuadoriin, joka on nyt pääpiirteittäin valmis. Jos se menestyisi jostain syystä, niin myyntiilmoituksia voi olla jotain muutamia miljoonia kappaleita, eli suunnilleen sitä koko luokkaa pitää jo varmaan täältä käsin suunnitella?

Taulurakenne toimii siten, että on taulu product(myynti-ilmoitus) jolla on n-kappaletta(usein joku 40) ns. tageja(avainsanoja), eli produktiin liittyviä ominaisuuksia, kuten esimerkiksi tietokoneella voisi olla tagit: HP, Pavillion, dvd, webkamera, microphoni, akku, laturi, valkoinen jne...

Nyt teen hakutoimintoa, että esimerkiksi hakusanalla: kannettava HP valkoinen,
tulisi kaikki ilmoitukset, joissa on edellä mainitut tagit. Näin ollen ihmisille tulisi lista HP:n valkoisista kannettavista.

Tagikokonaisuuteen liittyy vielä jotain tauluja(yhteensä product, tag, tagValue, datatype), että rakenne olisi optimaallinen, jota en selkeyden vuoksi kuitenkaan täällä ole esittänyt nyt miten se toimii, ellei joku sitten pyydä.

Ongelma on MySQL kyselyn optimointi oikeiden hakutuloksien löytämiseksi.
Itse olen lähestynyt ongelmaa kahdesta suunnasta ja haluaisin kysyä teidän analyysia siitä, että kumpi on parempi, tai jos teillä olisi vielä parempi ehdoitus.

1. Lähestymistapa:

Teen kyselyn, jossa JOIN:naan kaikki tagit(alitauluineen) productiin. Laitan
where ehdoksi (tagValue.name LIKE 'HP' OR tagValue.name LIKE 'valkoinen' OR tagValue.name LIKE 'kannettava'), jolloin lista näyttäisi sunnilleen tältä:

product.idProduct | tag.value  | tagValue.name
1005                  1              HP
1005                  1           valkoinen
1006                  1              HP
1006                  1           kannettava
1006                  1            valkoinen
....
suunilleen n. 100 000 riviä.

Sitten PHP koodilla niputtaisin vain ne rivit lopputulokseen, jonka idProduct on ilmaantunut kahdessa muussa rivissä myös. Eli esim. procut 1005 ei tulisi tuloksiin, koska se esiintyy vain kahdessa rivissä, mutta sen sijaan id. 1006 esiintyy kolmessa rivissä, niin se otetaan, koska hakusanojakin oli kolme.

2. Lähestymistapa

Etsin ensiksi productien tagValue.namesta sanaa 'kannettava', koska se on niin sanottu product tyyppi, eli ei mikään kategoriaan tai ominaisuuteen liittyvä sana. Sanotaan, että tuloksia voisi tulla vaikka 20 000 kannettavaa.

Sen jälkeen kävisin loopissa kaikki nämä 20 000 productia läpi ja kattoisin,
jos kaksi muutakin tagia löytyisi productilta. Eli tekisin 20 000 uutta kyselyä, jossa joinaan product tagValueen ja etsin ehdolla, id = x ja tagValue.name = "HP" tai "valokoinen".

Voisin kokeilla tehdä jonkinlaisia testejä aiheeseen liittyen, mutta jotkut osaa varmaan mututuntumalla heti heittää kumpi lähestymistapa on järkevämpi, kertoa omansa tai antaa kritiikkiä yleensä tuosta tagi hommasta.

Eli pelkään, että toi hakujuttu menee jumiin tosi nopeasti jos on vain yksi servu käytettävissä. Jos osaatte antaa jotain vinkkiä yleensä koko ajatukseen tai huomaatte, että en ole huomioinut jotain asiaa, niin mielellään kuulisin, niin ei sitten hommat kuse tuotannossa niin pahasti.

Kiitokset etukäteen!

Mitä jos laitat kaikki tagit yhteen sarakkeeseen vaikka välillä eroteltuna ja käytät hakemiseen MySQL:n Fulltext -hakua http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Eli en lähtisi keksimään pyörää uudestaan.

Jos kuitenkin on pakko, niin tekisin kyselyn niin, että MySQL antaa vastauksena suoraan vaan ne relevantit tuotteet ilman että tarvitsee isoja tietomassoja pyöritellä läpi PHP:llä.

lainaus:

Sitten PHP koodilla niputtaisin vain ne rivit lopputulokseen

Miksi ihmeessä se pitäisi tehdä PHP:llä kun MySQL:llä se käy nopeammin ja helpommin?

Kiitoksia Grez vastauksesta!

En ole MySQL speacialisti, koska käytän MVC Frameworkkeja usein tietokantakeskusteluun, niin osaisitko sanoa voisiko ne saada joinattua noi tagNamet yhteen sarakkeeseen SQL kyselyssä?

Eli voisiko ne todella saada tähän tyyliin jotenkin nippuun:

idProduct | tagValue.name(s)
 1006         HP Valkoinen Kannettava WLAN...

Koska ongelmahan on saada ne kaikki tietyn idProduct:in omaavat tagit yhteen sarakkeeseen.

En voi siis rakennetta muuttaa ja laittaa niitä tietokannassa yhteen sarakkeeseen kaikkia tageja, koska tagilla on muitakin ominaisuuksia ja tehtäviä, tässä vielä varalta miltä näyttää tietokanta rakenne:

DROP TABLE IF EXISTS `datatype`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `datatype` (
	`idDatatype` int(11) unsigned NOT NULL, /* AUTO_INCREMENT,*/
	`description` varchar(64) NULL COMMENT 'describres about the value.',
	`measure` varchar(32) NULL COMMENT 'sample: cm',
	`params` TEXT NULL COMMENT 'in dropdownlist there can be params',
	/*`datatype` varchar(16) NULL COMMENT 'Could be  ',*/
	`UItype` varchar(16) NULL COMMENT 'Could be: dropdownlist, chexbox, text-field...',
	`firstUnit` int(11) NULL,
	`lastUnit` int(11) NULL,
	`step` int(11) NULL COMMENT ' Can cause value list like: 100, 200, 300, 400, so step is 100.',


	PRIMARY KEY (`idDatatype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;



DROP TABLE IF EXISTS `tagValue`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tagValue` (
	`idTagValue` int(11) unsigned NOT NULL /* AUTO_INCREMENT */,
	`description` varchar(64) NULL COMMENT '---',
	`purpose` varchar(8) NULL COMMENT '1=category, 2=details, 3=place, 4=company, 5=article productType. Sample form: 11010000. The sample TagValue would be presented in category and details and company',
	`isVerified` int(1) NULL DEFAULT 1,

	`name` varchar(64) NULL COMMENT 'Can be like: length, widht, max-velocity, toyota, honda, area, high-quality...',
	`idDatatype` int(11) unsigned NOT NULL,

	FOREIGN KEY (`idDatatype`) REFERENCES `datatype` (`idDatatype`),
	PRIMARY KEY (`idTagValue`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;



DROP TABLE IF EXISTS `tag`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tag` (
	`idTag` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`idProduct` int(11) unsigned NULL COMMENT 'if it is product tag, the id is here',
	`idCompany` int(11) unsigned NULL COMMENT 'if it is company tag, the id is here',

	`idTagValue` int(11) unsigned NOT NULL,
	`purpose` int(1) unsigned NOT NULL COMMENT '1 = category, 2 = details, 3 = place, 4 = company...',
	`value` double NULL COMMENT 'Sample: 0, 1, 2, 1232, 1234412 - meaning of value depends on datatype in tagValue. See all saved datatypes.',

	PRIMARY KEY (`idTag`),

	KEY `tag.tagValue_constraint` (`idTagValue`),
	CONSTRAINT `tag.tagValue_constraint` FOREIGN KEY (`idTagValue`) REFERENCES `tagValue` (`idTagValue`) ON DELETE CASCADE


) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Niin, ainahan sellaisen tekstikentän voisi olemassa olevan tagirakenteen rinnalle.

Pitäisi mennä koisimaan niin en ala nyt sen enempää miettiä, mutta ihan tuohon esimmäiseen kyselyysi jos laittaisi selectiksi "Select product.idProduct" ja loppuun jotakuinkin "group by product.idProduct having count(product.idProduct)=3" niin saisit suoraan listan kohteista, joissa on kaikki kolme tagia. Tehokkaampi ratkaisu olisi ehkä joinata se tagitaulu riittävän monta kertaa. Riippunee sitten käyttämästäsi frameworkista miten se on helpointa tehdä.

Vai pitäiskö lisätä tuolla productille vielä toi sarake missä kaikki tagit nipussa ja päivittää sitä aina sitä mukaan kun tagit muuttuu. Olisi kyllä silloin kahdessa eripaikkaa sama informaatio?

Lisäys:

Sori, näin vastauksesi vasta kun lähetin viesti, eli tosiaan ton siis yksi mahdollisuus heittää uus sarake vielä siihen lisäksi.

Kiitti tuosta having countista, en tiennyt kyseistä! Meen varmaan aluksi sillä ja
jos tämä tuote menestyy, niin sitten kokeillen perästä muuttaa tehokkaammaksi, esim. laittamalla tuon extra sarakkeen sitte.

Grez kirjoitti:

Mitä jos laitat kaikki tagit yhteen sarakkeeseen vaikka välillä eroteltuna ja käytät hakemiseen MySQL:n Fulltext -hakua http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Toimiiko MySQL:n Fulltext-haku InnoDB:n kanssa?

No itse asiassa en juurikaan MySQLiä käytä, joten en ehkä ole paras henkilö kommentoimaan miten se toimii ja kannattaako sitä loppujen lopuksi käyttää. Nopeasti kun katsoin niin näyttäisi että ei toimi InnoDB:n kanssa.

Ehkä en ihan ymmärrä tuota kantarakennetta, mutta itse tekisin niin, että (jos on product jolla voi olla 0-N tagia): tagit on omassa taulussa, product on omassa taulussa ja näiden yhdistelmä on omassa aputaulussa product_tag.

PRODUCT: product_id, ja product tiedot
TAG: tag_id, ja tag tiedot
PRODUCT_TAG: product_id ja tag_id

En usko että tuo "full text search" on kovin nopea miljoonien rivien taulussa. Luulen, että tuo dimensio- ja aputaulu järjestely on nopeampi.

SELECT product.id,product.name,... FROM product
WHERE product.id IN (
  SELECT product_tag.id FROM product_tag
  WHERE tag_id IN (12,25,45)
  GROUP BY product_tag.id
  HAVING count(product_tag.id)=3  -- varastettu Greziltä ;)
)

nuo tuotteet sitten tulostetaan. Lisäksi tulisi varmaan tuota joukkoa jo kyselyssä rajoittaa, esim joko sanomalla, että liikaa tuloksia tai pagettamalla tuo, katsele LIMIT optiota mysql:ssä. Ei ihminen kuitenkaan halua sivulle kuin sen 20-50 tulosta, kaikki sen yli on ihan liikaa pienen ihmisen käsityskyvylle.

ZeroGravity, itseassiassa annoit hyvän idean, sen sijaan että joinailisin noi taulut, niin varmaan käyttää tuota sisäkkäistä kysely rakennetta:

//sanotaan, että tagValue 6 = "HP", tagValue 12 = "kannettava"...

				SELECT * FROM product
				WHERE product.idProduct IN (
					  SELECT tag.idProduct FROM tag
					  WHERE tag.idTagValue IN (6,12, 20)
					  GROUP BY tag.idProduct
					  HAVING count(tag.idProduct)=3
				)

Sen sijaan, että kirjottaisin näin, miten on tällä hetkellä:

//searchClauseen ja seachCount:ttiin siin muuttujat PHP:ltä.

"SELECT * FROM
				product JOIN(
					tag JOIN tagValue ON tag.idTagValue = tagValue.idTagValue
				) ON product.idProduct = tag.idProduct
				where ($searchClause)
                                group by product.idProduct having count(product.idProduct)>= $clauseCount
			"

Mutta en tiedä olisko tuosta aputaulusta apua? Koska jokatapauksessa tagi taulustahan lähtisi kyselyyn vain kaksi kenttää: idProduct ja idTagValue. Mutta sen sijaan toi sun kyselysmuoto näyttää ehkä optimammiselta, mitä omani? (tuossa omassani on vähän kussu noi sisennykset, että jaksaakohan erkkikään sitä vilkaista.)

EDIT:

Tein itseasiassa testeja ja JOINailu näyttää paljon tehokkaammalta kuin sisäkkäiset kyselyt, keskimmäärin kolminkertainen aikaero tuli, vaikka tosin
testit ei ole kovin relevantteja, koska rivejä on olemassa koko kannassa vain muutama hassu. Olisin kuitenkin odottanut päinvastaisia tuloksia.

Jokatapauksessa, pistän molemmat muodot talteen ja kokeilen sitten myöhemmin suuremmilla massoilla, koska voi olla, että tilanne kääntyy silloin päinvastaiseksi.

Joinailu yleensä on tehokkaampaa kuin alikyselyt. Joskus alikyselyt voi olla nopeampia jos tietokantaa ei muuten saa optimoimaan kyselyä järkeväksi.

Aivan, kiitti Grez, ollut iso apu teistä.

Juu en ole mikään SQL guru. Join tai alikysely... joka tapauksessa katso sitä limittiä, turhaan sitä kaikkia 5000 hakee, et niitä kuitenkaan näytä ruudulla vaan se 20-50 sivullaan?

SELECT id,nimi,hinta FROM tuote LIMIT 200,50;

Niin ja aputauluhan ja alikysely tuossa - ne on kaksi eri asiaa. Ihan vaan tarkistaakseni, että ollaan samalla sivulla. Aputaulua kannatan kuitenkin, indeksoituna ton tagin id:n mukaan (veikkaisin), jos sen kyselyn tekee sit vaikka joinilla.

Vielä tuon tämän "ikivanhan" aiheen ylöspäin, kun innostuin tekemään sellaista SQL työkalua, joilla noita ko. SQL:iä saisi testattua. Ja innostuinkin vähän enemmän, koodi löytyy tuolta. "Koko p4sk4" on yhdessä filussa :P (kun sen piti olla vain pieni koodinpätkä, jne).

http://pastebin.com/Mpiwxz2r

Sillä härpäkkeellä voi testata aika helposti muutaman tuhannen (~20K) rivin tauluja. Oma napsutteluinto on vain tiellä. Omassa läppärissä tuo toimi suht nopeesti. Koodi on vapaata (ja sen näköistä :D). Tuossa yksi kuvatus ko. scriptiin:

http://imageupload.org/?d=E546BC7B1

Härpäkkeen käyttö on suht yksinkertaista. Ensin tulee asettaa konfiguraatio kuntoon (riveillä 40-50) ja sitten 'Recreate schema', ja sitten 'add 1000 products' N-kertaa. Sitten SQL-kyseilytä perään (mitä nyt sitten ikinä keksii). Relaatioita ei ole, saa tehdä ketä huvittaa. Koodi on IMO suht simppeliä.

Mutta sitten asiaan. Tuo ehdottamani SQL oli aivan hirveä ... aikaa kului niin kauan etten jaksanut odottaa (temppitaulua se oli ilmeisesti koostamassa, niin väitti, killasin koko prosessin sieltä). Joten varmaankin jotain tällaista olisin ehdottamassa :D (ton härpäkkeen taulujen mukaan).

SELECT p.name,pt.product_id FROM product p, product_tag pt WHERE pt.tag_id IN (22,25,30) AND pt.product_id = p.id GROUP BY pt.product_id HAVING count(pt.product_id)=3