Vise where nad velikom tabelom

Vise where nad velikom tabelom 21.03.2019. u 00:48 · pre 92 meseca #1

Pozdrav svima,

ovako, ovo je dizajn forme i tabele:

Tabela sa proizvodima ima oko 1.3M redova.
Website je dropdwon, imace 7 - 8 sajta (URL, amazon.com, amazon.co.uk....)
First Found i Last Scrapped su datumi, klik na njih otvara range, dakle datum "od" i "do" i tako za oba datuma
Da li sacuvati kao integer (unixtimestamp) ili date field type?
Price, je takodje range, kada se klikne ispod se pokazu 2 input field-a, "od" i "do", moguce vrednosti su decimal od 0.1 do 100k
Wishlist (integer od 1 do 100k), Review Score (decimal od 1, 1.1, 1.2 do 4.8, 4.9, 5)
Filter za ove 3 kolone idu po range-u, isto kao za Price
Category izbor kategorije (iscupam jedinstvene vrednosti iz tabele)

Gubim se oko ovih filtera, kako ovo uraditi najbolje, ne mogu na sve kolone da stavim index, a ako koristim multiple columns?
Ako uradim ovako:

Code:
ALTER TABLE `pages` ADD INDEX `numberOne` (`website`, `date_found`, `date_scrapped`, `price`, `wishlist`);

buni me, jer u formi moze da selektuje website ali ne i date scrapped na primer, na filter moze da sadrzi jednu kolonu, sve kolone ili samo neke tri na primer.
Opet imam range, pa bi neki query izgleda ovako ako je user izabrao website, napravio range za price i izabrao samo review score "from" field:

Code:
SELECT * FROM `products` WHERE `website` = 'amazon.co.uk' AND `price` > 10 AND `price` < 20 AND `review_score` > 4.5

Kako bi ste resili ovo a da radi brzo?

Re: Vise where nad velikom tabelom 21.03.2019. u 01:34 · pre 92 meseca #2

dizajn forme je nebitan, a dizajn tabele se sa te slike ne vidi

mozes da imas nekoliko razlicitih kompozitnih indexa, posebno sto tabela nije velika i sto nemas tu neki ogroman insert flow

cena i score nema svrhe da ti budu indexirani

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 21.03.2019. u 01:53 · pre 92 meseca #3

Da znam da je nebitan dizajn, cisto sam hteo da pokazem sta sve ulazi u filter.
Kada kazes da mogu da imam vise razlicitih kompozitnih indexa, koliko da ih napravim?
Ispravi me ako gresim, mislim da je bitno kada koristim where da je bitan i redosled?
Tipa, nije isto ako je index, po websites, category, date_scrapped onda where bi trebalo da bude where website = 'site' and category 'usb'?
Mislim kako onda radi ako user nije izabrao category?
Bas ovo mi je problem, sta ako user ne odabere neki filter a taj filter je index koji je prvi po redu u kompozitnom indeksu?
Ili npr. sta ako koristi samo range za price a kazes da nema smisla da bude indeksiran, zar onda nece da prodje kroz celu tabelu?
I jbm mu sunce, kako ih vise napravim, koliko kombinacija ima?
Mozda lupam :/

Re: Vise where nad velikom tabelom 21.03.2019. u 02:17 · pre 92 meseca #4

redosled u where je nebitan, redosled u kompozitnom indexu je bitan...
pricao sam i pisao do sada mnogo puta za kompozitne, dakle ako imas

where a=1 and b=2 and c=3 and d > 10

moguci indexi koji se koriste za to su

(a,b,c,d)

(a,c,b,d)

(b,c,a,d)

(b,a,c,d)

(c,a,b,d)

(c,b,a,d)

(valjda nisam neki propustio :D )

ako imas inded (a,b,c,d) ono gde on moze da se koristi je

a=1 and b=2 and c=3 and d> 4

a>1

a=1

a=1 and b=2

a=1 and b>2

a=1 and b=2 and c>3

a=1 and b=2 and c=3

ako imas samo npr

b=2

tu taj index ne moze da se koristi

ako imas

where a>1 and b>1

tu ce se koristiti ili neki index koji pocinje sa a ili index koji
pocinje sa b tako sto ce optimizer odluciti gde mu je bolja kardinalnost
/ gde ce vise odseci tim indexom a za drugi uslov onda kroz taj rezultat
radi klasican scan (bez indexa)

koliko indexa, koliko oces :D zavisi kakvi su ti upiti :D ... dodatni
index ti usporava insert sto tebi nije problem jer imas patetican broj
inserta u minuti, dodatni index ti zauzima ram sto danas ne bi trebalo
da ti je rpoblem kada su serveri sa 128G rama dostupni za sica pare a
cela baza ti je ispod 2 miliona slogova sto je sitno..

ono sto je dobro je sto mozes da pravis index statistiku pa da izbacis
indexe koje ne koristis

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

2

Re: Vise where nad velikom tabelom 21.03.2019. u 02:29 · pre 92 meseca #5

Hvala Bogdane :)

Jasnije je sada :)

Dakle kombinacije ne ginu, a sta mi je sada palo na pamet, mozda je teska glupost :)
Sta mislis, da imam jedan kompozitni indeks? a, b, c, d, e
e sad, ako user nije izabrao nista za a, ja to ishendlam u app-u, i kazem where a >= najmanja vrednost za a kolonu, tipa jednom dnevno opicim kron i sacuvam najmanju vrednost za svaku kolonu?
ove textove smestim na kraju u indexu?

Jel ovo ima smisla ili ne?

Najvise me buni, jer ne znam kakvi ce tacno upiti da budu, bukvalno where moze da ima bilo koju kolonu, jednu ili vise, i kako da to sve pokriem sa indexima :(

Re: Vise where nad velikom tabelom 21.03.2019. u 02:56 · pre 92 meseca #6

nema smisla, znaci ako imas index (a,b,c,d) i user trazi samo
filtriranje po a to ce da koristi taj index najnormalnije nema ti sta da
dodajes tu .. a ako imas index (a,b,c,d) i ti u where kazes a>1 dalje za
b,c i d se ne koristi index vec samo za a ... dakle kompozitni index
vazi za == od pocetka i prestaje da se koristi zavrsno sa prvim range >
ili < ... dakle ti ako kazes a>1 svi ostali uslovi za taj upit ne
koriste index

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 21.03.2019. u 03:00 · pre 92 meseca #7

inace za pretrage tog tipa, user obicno hoce da kucne tekst sta ga
zanima i da vidi poredjenje na sajtovima, bole ga uvo dal je ovaj ili
onaj sajt i slicno ... eventualno oce pored teksta da kucne cenu od do i
to je to .. tako da je bolje tu dodas jedan full text index ... ili
koristis neki externi elastic search ili sphinx ili ... za search nego ...

posto vidi, ti ovde imas upit gore "product name", i to ti je neki
match, taj index ce ti se koristiti 99% vremena i svi ovi ostali filteri
se "nece koristiti uopste" bez obzira koliko ih napravis, on ce ti
odraditi FTS kroz product name (nadam se da ti je to FTS a ne like %$xx%
) i sve ostale filtere ce radi klasican scan kroz result set

tako da napravis po jedan single index za svaku kolonu i fts index na name
i to je to

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 21.03.2019. u 04:10 · pre 92 meseca #8

da jasno :)

turio sam FTS nad title colonom
metnuo i review score index single
like odavno ne korisitm mada i FTS nisam neko vreme, pozaboravljao sam stvari:D
dakle ovako

Code:

ALTER TABLE `trending` ADD FULLTEXT(`title`);
ALTER TABLE `trending` ADD INDEX(`review_score`);

SELECT * FROM `trending` WHERE MATCH(title) AGAINST('knife') and review_score > 4.5 limit 50

tabela ima 1350585 redova, i ovaj upit traje 0.0158 seconds

explain kaze:

mislim da je ok?

e sada, kada mu ovo opalim:

Code:

SELECT * FROM `trending` WHERE MATCH(title) AGAINST('kn') and review_score > 4.5 limit 50

dakle umesto 'knife' stavim 'kn' onda izbaci empty set, to je bese zato sto se kn nalazi u vise od 50% redova?
ali kad probam sa IN BOOLEAN MODE, isto empy, zar ne bi trebalo da vrati neki result?

ako user nije nista nije uneo u title za product, ne bi trebalo da stavljam MATCH u query? nema smisla, jel tako? :)

ovo je moj localhost komp, win10, 10.1.38-MariaDB, xampp, sve default
probacu i na serveru, iscimam admina da digne server pa da vidim tamo, mozda bude bolje (performance)

Re: Vise where nad velikom tabelom 21.03.2019. u 04:47 · pre 92 meseca #9

ne nego zato sto ti je min za FTS po defaultu 3 slova (mozes da smanjis
ali nema svrhe)

da, ako je sadrzaj prazan treba da nemas taj match

baci to go..no od marije, stavi ili percona mysql ili original oraclov mysql

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 21.03.2019. u 08:21 · pre 92 meseca #10

Ovde ti ne treba ni jedan indeks osim full text search.

Sve ostalo ima malu kardinalnost u odnosu na veličinu tabele.
Ne znam kakvi su ti podaci, možda ima smisla indeks nad datumstkim poljima, ali nije sve u kardinalnosti, pitanje je koliko često se ona koriste u uslovima pretrage.

Re: Vise where nad velikom tabelom 21.03.2019. u 12:59 · pre 92 meseca #11

hvala puno, odradio sam tabelu, za sada svaka kolona je index, i fts nad title
podigao percona mysql na serveru
da sredim jos front-end pa cu da im da koriste
javljam kako bude :)

Re: Vise where nad velikom tabelom 18.09.2019. u 22:18 · pre 86 meseci #12

Zdravo,

evo me mene ponovo, dugo me nije bilo :)

ovo je radilo fino :)

medjutim, sada imam novi zahtev...

treba da se sacuva i history za svaki proizvod

do sada je bilo import svakog dana i onda update

medjutim za ovu tabelu treba history za price, review i likes
pored toga, na osnovu trenutne vrednost da se izracuna procenat povecanja ili smanjenja za review i likes
tipa za likes, sada je 100, dodje novi import od 150 i to je povecanje od 50% i tako za svaki import

ja sam to uradio ovako

imam trending tabelu i u njoj, id, reviews, likes, price i x nekih kolona (tipa title, description, categoru etc...)
na import (CSV file) uhvatim likes i reviews (trenutne vrednost) i to updejtujem u trending tabelu uz jos x drugih kolona

e sad imam i trending history sa kolonama:

id, product_id, price, likes, reviews, likes_increase, reviews_increase, date

na import za svaki proizvod select * from trending_history where product_id = x
i onda izvrtim to kroz petlju u app (php) i izracunam za svaki row koliko je plus/minus povecanje za likes i reviews i onda napravim batch update za trending history tabelu

i to nekako radi

na front-u imam filter po datumu i sort by review or likes asc or desc
otprilike daj mi najveci % increase za likes za datum 1 septembar

ovako mu dodje SQL query

Code:
select trending.*, trending_history.likes_increase from trending join trending_history ON trending_history.product_id = trending.id WHERE date = 1567296000 order by trending_history.likes_increase DESC

e sada ovo radi ali ubija koliko je sporo i ako sam dodao index po likes_increase, reviews_increase :( :(

Neko neku ideju kako ovo uraditi a da radi fino? :)

Re: Vise where nad velikom tabelom 19.09.2019. u 21:11 · pre 86 meseci #13

Sta je sporo? Taj join ili nesto drugo?

Re: Vise where nad velikom tabelom 19.09.2019. u 23:11 · pre 86 meseci #14

Da ovaj query generalno, koji mi je i najjbitniji.
Kontam da je zbog order by nad velikom tabelom, ali to to je pod indexom.
Imam tu oko 40m redova u history.
Ne znam do cega je tako, da li model baze ili nesto sl, zato sam i postavio pitanje :)

Re: Vise where nad velikom tabelom 19.09.2019. u 23:37 · pre 86 meseci #15

Citat:

svepomalo:
e sada ovo radi ali ubija koliko je sporo i ako sam dodao index po likes_increase, reviews_increase :( :(

da mi ne bi sada lili stravu, bacali pasulj, gledali u soljicu i ostale srbistanske tehnike za izvlacenje informacija, aj ti lepo nama daj explain tog upita ..

https://dev.mysql.com/doc/refman/8.0/en/explain.html

daj nam i TREE i JSON izlaz

a onda daj izlaz od EXPLAIN ANALYZE

https://dev.mysql.com/doc/refm...n/explain.html#explain-analyze

dakle da rezimiramo

EXPLAIN FORMAT=JSON SELECT ...
EXPLAIN FORMAT=TREE SELECT ...
EXPLAIN ANALYZE SELECT ...

za format=tree mora imas 8.0.17+
za analyze mora imas 8.0.18+

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 20.09.2019. u 00:36 · pre 86 meseci #16

ahahah jes vala :)

Evo informacija :)

Server: 5.7.26-29-log - Percona Server (GPL), Release 29, Revision 11ad961

Explain:

Code:
mysql> EXPLAIN SELECT `trending`.`id`, `trending`.`asin`, `trending`.`url`, `trending`.`title`, `trending`.`brand`, `trending`.`category`, `trending`.`image`, `trending`.`price`, `trending`.`likes_overall`, `trending`.`review_score`, `trending`.`review_score_overall`, `trending`.`date_found`, `trending`.`date_srapped`, `trending`.`saved`, `trending`.`date_saved`, `trending_overall`.`likes`, `trending_overall`.`review`, `trending_overall`.`overall_review_percentage` as `review_score_daily`, `trending_overall`.`overall_likes_percentage` as `likes_daily` FROM `trending` JOIN `trending_overall` ON `trending_overall`.`product_id` = `trending`.`id` WHERE `trending_overall`.`date` = 1568662153 ORDER BY `review_score_daily` DESC  LIMIT 50;
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
| id | select_type | table            | partitions | type   | possible_keys   | key                       | key_len | ref                                          | rows | filtered | Extra       |
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | trending_overall | NULL       | index  | product_id,date | overall_review_percentage | 5       | NULL                                         | 1432 |     3.49 | Using where |
|  1 | SIMPLE      | trending         | NULL       | eq_ref | PRIMARY         | PRIMARY                   | 8       | admin_competitor.trending_overall.product_id |    1 |   100.00 | NULL        |
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

EXPLAIN FORMAT=JSON

Code:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2826400.61"
    },
    "ordering_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "trending_overall",
            "access_type": "index",
            "possible_keys": [
              "product_id",
              "date"
            ],
            "key": "overall_review_percentage",
            "used_key_parts": [
              "overall_review_percentage"
            ],
            "key_length": "5",
            "rows_examined_per_scan": 1432,
            "rows_produced_per_join": 3338582,
            "filtered": "3.49",
            "cost_info": {
              "read_cost": "1470528.00",
              "eval_cost": "667716.40",
              "prefix_cost": "2138244.40",
              "data_read_per_join": "458M"
            },
            "used_columns": [
              "id",
              "product_id",
              "likes",
              "review",
              "overall_likes_percentage",
              "overall_review_percentage",
              "date"
            ],
            "attached_condition": "((`admin_competitor`.`trending_overall`.`date` <=> 1568662153))"
          }
        },
        {
          "table": {
            "table_name": "trending",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "8",
            "ref": [
              "admin_competitor.trending_overall.product_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3338582,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "3338582.00",
              "eval_cost": "667716.40",
              "prefix_cost": "6144542.80",
              "data_read_per_join": "10G"
            },
            "used_columns": [
              "id",
              "asin",
              "url",
              "title",
              "brand",
              "category",
              "image",
              "price",
              "likes_overall",
              "review_score",
              "review_score_overall",
              "date_found",
              "date_srapped",
              "saved",
              "date_saved"
            ]
          }
        }
      ]
    }
  }
}

A nece za EXPLAIN FORMAT=TREE i EXPLAIN ANALYZE :(

Evo i tabela:

Code:

CREATE TABLE `trending` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `asin` varchar(30) NOT NULL DEFAULT '',
  `url` text,
  `title` varchar(255) NOT NULL DEFAULT '',
  `brand` varchar(255) NOT NULL DEFAULT '',
  `category` varchar(255) NOT NULL DEFAULT '',
  `image` varchar(255) NOT NULL DEFAULT '',
  `price` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
  `likes` int(6) unsigned NOT NULL DEFAULT '0',
  `likes_overall` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `reviews` int(6) unsigned NOT NULL DEFAULT '0',
  `review_score` decimal(10,2) NOT NULL DEFAULT '0.00',
  `review_score_overall` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `date_found` bigint(20) unsigned NOT NULL DEFAULT '0',
  `date_srapped` bigint(20) unsigned NOT NULL DEFAULT '0',
  `saved` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `date_saved` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `asin` (`asin`),
  KEY `review_score_overall` (`review_score_overall`),
  KEY `likes_overall` (`likes_overall`),
  FULLTEXT KEY `title` (`title`,`category`)
) ENGINE=InnoDB AUTO_INCREMENT=1718407 DEFAULT CHARSET=utf8

Code:

CREATE TABLE `trending_overall` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `asin` varchar(30) NOT NULL DEFAULT '',
  `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `likes` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `review` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `overall_likes_percentage` decimal(10,2) NOT NULL DEFAULT '0.00',
  `overall_review_percentage` decimal(10,2) NOT NULL DEFAULT '0.00',
  `date` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `date` (`date`),
  KEY `review` (`review`),
  KEY `overall_review_percentage` (`overall_review_percentage`),
  KEY `likes` (`likes`)
) ENGINE=InnoDB AUTO_INCREMENT=97530591 DEFAULT CHARSET=utf8

Re: Vise where nad velikom tabelom 20.09.2019. u 00:43 · pre 86 meseci #17

za pocetak, matoro ti je to, idi na 8.0.xx .. bolji je za 5 klasa

za TREE i ANALYZE ti treba noviji mysql

sad, pre nego odradis upgrade u buducnosti, jedino sto mozes je da
uradis profiling .. znaci isprati pa baci rezultate:

https://dev.mysql.com/doc/refm...ce-schema-query-profiling.html

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 20.09.2019. u 01:16 · pre 86 meseci #18

pa da, uradicu...
evo rezultata iz profiling-a:

Code:

+--------------------------------+------------+
| Stage                          | Duration   |
+--------------------------------+------------+
| stage/sql/starting             |   0.000032 |
| stage/sql/checking permissions |   0.000001 |
| stage/sql/Opening tables       |   0.000005 |
| stage/sql/init                 |   0.000011 |
| stage/sql/System lock          |   0.000002 |
| stage/sql/optimizing           |   0.000005 |
| stage/sql/statistics           |   0.000043 |
| stage/sql/preparing            |   0.000007 |
| stage/sql/executing            |   0.000000 |
| stage/sql/Sending data         |   0.000027 |
| stage/sql/end                  |   0.000000 |
| stage/sql/query end            |   0.000002 |
| stage/sql/closing tables       |   0.000002 |
| stage/sql/freeing items        |   0.000020 |
| stage/sql/cleaning up          |   0.000000 |
| stage/sql/starting             |   0.000077 |
| stage/sql/checking permissions |   0.000001 |
| stage/sql/checking permissions |   0.000002 |
| stage/sql/Opening tables       |   0.000012 |
| stage/sql/init                 |   0.000022 |
| stage/sql/System lock          |   0.000004 |
| stage/sql/optimizing           |   0.000005 |
| stage/sql/statistics           |   0.002840 |
| stage/sql/preparing            |   0.000018 |
| stage/sql/Sorting result       |   0.000004 |
| stage/sql/executing            |   0.000000 |
| stage/sql/Sending data         | 166.791883 |
| stage/sql/end                  |   0.000001 |
| stage/sql/query end            |   0.000005 |
| stage/sql/closing tables       |   0.000005 |
| stage/sql/freeing items        |   0.000019 |
| stage/sql/logging slow query   |   0.000047 |
| stage/sql/cleaning up          |   0.000000 |
| stage/sql/starting             |   0.000026 |
| stage/sql/checking permissions |   0.000001 |
| stage/sql/Opening tables       |   0.000004 |
| stage/sql/init                 |   0.000010 |
| stage/sql/System lock          |   0.000002 |
| stage/sql/optimizing           |   0.000004 |
| stage/sql/statistics           |   0.009030 |
| stage/sql/preparing            |   0.000017 |
| stage/sql/executing            |   0.000000 |
| stage/sql/Sending data         |   0.000054 |
| stage/sql/end                  |   0.000001 |
| stage/sql/query end            |   0.000005 |
| stage/sql/closing tables       |   0.000006 |
| stage/sql/freeing items        |   0.000299 |
| stage/sql/cleaning up          |   0.000000 |
+--------------------------------+------------+
48 rows in set (0.00 sec)

Re: Vise where nad velikom tabelom 20.09.2019. u 01:22 · pre 86 meseci #19

aj sad pre nego dobijes nesto "gotovo", da li ti je iz ovih podata jasno bar sta se desava?

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Vise where nad velikom tabelom 20.09.2019. u 01:28 · pre 86 meseci #20

pa mislim da jeste, ovo "Seding data" sto je najduze znaci da rovari po disku, da nije pokriven indexom?
Ako je to slucaj, cudi me zasto, jer mi je date u overall tabeli index, takodje i review_score_daily

Po meni bi trebalo ovako da razmislja baza, ok imam date i to je index to cu sada da izdvojim, onda to sortiram, a i za to imam index pa cu brzo da odradim, product id po koji ide join u drugu tabelu je isto index.....

lupam? :)

ako ne lupam, ne kontam zasto je kilavo :/

Forum kao aplikacija na telefonu

Srodne teme