Vise where nad velikom tabelom

[ svepomalo @ 20.03.2019. 23:48 ] @

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?

[ bogdan.kecman @ 21.03.2019. 00:34 ] @

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

[ svepomalo @ 21.03.2019. 00:53 ] @

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 :/

[ bogdan.kecman @ 21.03.2019. 01:17 ] @

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

[ svepomalo @ 21.03.2019. 01:29 ] @

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 :(

[ bogdan.kecman @ 21.03.2019. 01:56 ] @

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

[ bogdan.kecman @ 21.03.2019. 02:00 ] @

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

[ svepomalo @ 21.03.2019. 03:10 ] @

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)

[ bogdan.kecman @ 21.03.2019. 03:47 ] @

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

[ djoka_l @ 21.03.2019. 07:21 ] @

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.

[ svepomalo @ 21.03.2019. 11:59 ] @

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

[ svepomalo @ 18.09.2019. 21:18 ] @

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? :)

_{[Ovu poruku je menjao svepomalo dana 19.09.2019. u 00:05 GMT+1]}

[ Branimir Maksimovic @ 19.09.2019. 20:11 ] @

Sta je sporo? Taj join ili nesto drugo?

[ svepomalo @ 19.09.2019. 22:11 ] @

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

[ bogdan.kecman @ 19.09.2019. 22:37 ] @

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+

[ svepomalo @ 19.09.2019. 23:36 ] @

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

[ bogdan.kecman @ 19.09.2019. 23:43 ] @

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

[ svepomalo @ 20.09.2019. 00:16 ] @

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)

[ bogdan.kecman @ 20.09.2019. 00:22 ] @

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

[ svepomalo @ 20.09.2019. 00:28 ] @

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 :/

[ bogdan.kecman @ 20.09.2019. 00:47 ] @

da sending data je to rovari po disku .. explain ti kaze "Using where" iliti "ne koristim indexe"

Code:

...
FROM   `trending`
       JOIN `trending_overall`
         ON `trending_overall`.`product_id` = `trending`.`id`
WHERE  `trending_overall`.`date` = 1568662153
...

znaci koristis za join key:
trending_overall.product_id -> KEY `product_id` (`product_id`),
trending.id -> PRIMARY KEY (`id`)

za where teoretski mozes da koristis:
KEY `date` (`date`)

a pitanje dal ce ga koristiti ili ne od kardinalnosti

i onda ceo taj rezultat:

Code:

...
ORDER BY `review_score_daily` DESC
LIMIT 50;

sortiras i vracas prvih 50 ..

pitanje .. sta ti kaze

Code:

SELECT COUNT(*)
FROM   `trending`
       JOIN `trending_overall`
         ON `trending_overall`.`product_id` = `trending`.`id`
WHERE  `trending_overall`.`date` = 1568662153;

dalje, uradi

Code:

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;

koliko traje i na sta mu lici explain

onda

Code:

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`,
       `tdo`.`likes`,
       `tdo`.`review`,
       `tdo`.`overall_review_percentage` AS `review_score_daily`,
       `tdo`.`overall_likes_percentage`  AS `likes_daily`
FROM   `trending`
       JOIN (SELECT `product_id`,
                    `likes`,
                    `review`,
                    `overall_review_percentage`,
                    `overall_likes_percentage`
             FROM   `trending_overall`
             WHERE  `date` = 1568662153) tdo
         ON `tdo`.`product_id` = `trending`.`id`
ORDER  BY `review_score_daily` DESC
LIMIT  50;

daj vreme i explain za ovaj zadnji

[ svepomalo @ 20.09.2019. 01:04 ] @

a pa dobro, nisam se nalupao :)

Citat:

pitanje .. sta ti kaze

Code:
SELECT COUNT(*)
FROM   `trending`
       JOIN `trending_overall`
         ON `trending_overall`.`product_id` = `trending`.`id`
WHERE  `trending_overall`.`date` = 1568662153;

kaze

Code:
+----------+
| COUNT(*) |
+----------+
| 1716665 |
+----------+
1 row in set (14.99 sec)

a explain je:

order by samo bez where date = 1568662153 je 50 rows in set (0.00 sec) i explain:

vreme za zadnji upt je 50 rows in set (46.04 sec) i explain:

jel date index treba da se isforsira?

[ svepomalo @ 20.09.2019. 01:11 ] @

ako uradim ovo:

Code:

      `tdo`.`overall_likes_percentage`  AS `likes_daily`
FROM   `trending`
       JOIN (SELECT `product_id`,
                    `likes`,
                    `review`,
                    `overall_review_percentage`,
                    `overall_likes_percentage`
             FROM   `trending_overall` FORCE INDEX(`date`) -- force index
             WHERE  `date` = 1568662153) tdo
         ON `tdo`.`product_id` = `trending`.`id`
ORDER  BY `review_score_daily` DESC
LIMIT  50;

onda 50 rows in set (5.35 sec) i explain:

[ bogdan.kecman @ 20.09.2019. 01:37 ] @

nesto tu nije ok ....

taj subselect

Code:

SELECT `product_id`,
                    `likes`,
                    `review`,
                    `overall_review_percentage`,
                    `overall_likes_percentage`
             FROM   `trending_overall` FORCE INDEX(`date`) -- force index
             WHERE  `date` = 1568662153) tdo

nema koji drugi index da koristi nego date, on iz nekog razloga odbija da ga uzme u obzir... pusti "ANALYZE TABLE" za obe te tabele pa probaj kako ce se ponasa bez force index-a ...

takodje probaj samo ovaj subselect sta kaze explain (bez force index-a) i koja su vremena sa i bez force .. pre i posle analyze

takodje, ako te ne mrzi, svuci 8.0.17 u lokal i importuj te dve tabele pa probaj kako se ponasa tamo (pravi 8.0.17 sa dev.mysql.com)

_{[Ovu poruku je menjao bogdan.kecman dana 20.09.2019. u 02:54 GMT+1]}

[ svepomalo @ 22.09.2019. 21:42 ] @

evo me, sad se vratio sa puta pa kasnim sa odgovorom :/

subselect sa i bez force index-a je 50 rows in set (0.00 sec)

a isti je eplain sa i bex force

analyze kaze:

isti je explain i posle analyze

generalno, cenis da je nesto do ove verzije mysql-a?
nesto oko samog modela baze?
ako je to ok, index-i su ok i trebalo bi da iscepa ovaj query? :)

probacu sutra sa 8.0.17 pa javljam

[ svepomalo @ 22.09.2019. 21:50 ] @

Sto se tice 8.0.17 sa dev.mysql.com, neki specific savet za konfiguraciju ili da sve cepim po default-u?
Inace je server 32GB RAM-a i ntel Core i7-4770

[ svepomalo @ 24.09.2019. 18:04 ] @

ovako sta sam uspeo da 'ulovim':

Code:

SELECT * FROM trending_overall WHERE date = 1568662153 ORDER BY overall_review_percentage DESC;

ovo ne radi kako treba, upit traje preko minut a epxlain kaze:

ako izbacim sort by:

Code:

SELECT * FROM trending_overall WHERE date = 1568662153;

onda je lepo i brzo

takodje i ovo radi dobro (bez where po date):

Code:

SELECT * FROM trending_overall ORDER BY overall_review_percentage DESC LIMIT 50;

asto nece da radi kad su 'zajedno' date i order by kada je i jedno i drugo index, nije mi jasno :/

inace sada bas skidam i instaliravam 8.0.17 sa dev.mysql.com pa cu probati

[ Branimir Maksimovic @ 24.09.2019. 19:32 ] @

Digni indeks nad "overall_review_percentage", tad nece morati da sortira...

[ svepomalo @ 24.09.2019. 22:06 ] @

Citat:

Branimir Maksimovic: Digni indeks nad "overall_review_percentage", tad nece morati da sortira...

pa imam index nad tom kolonom :/

[ bogdan.kecman @ 24.09.2019. 22:33 ] @

Citat:

svepomalo:
asto nece da radi kad su 'zajedno' date i order by kada je i jedno i drugo index, nije mi jasno :/

mysql ce u JAKO RETKOM slucaju koristiti 2 indexa, inace koristenje indexa za order je mac sa dve ostrice, u ovom slucaju ocigledno reseno da se ne koristi (vidis da kaze filesort)

vidi kako ce 8.0.17 da se ponasa, on to ima malo bolje odradjeno, ali mrka kapa za taj sort... (tu je psql dosta bolji exekutor od mysql-a) ... probao bi ja ali tu ponasanje zavisi od date a ja nemam tvoju datu, eventualno odradi jedan analyze pre tih upita cisto da te ne je123 losa statistika ako te to zapalo

inace, u zavisnost za to koliko cesto imas taj deo date= u upitima i koliki ti je span datuma mozda mozes da particionises tu tabelu po datumu da dobijes na ubrzanju

[ svepomalo @ 24.09.2019. 22:49 ] @

e jbm mu sunce :/

ajde okacicu dump ove 2 tabele a uradje je:

Code:

mysqldump -u root -p competitor trending trending_overall > trending.sql

jel je ok ovako, ili treba jos nesto?

inace ako sam te dobro razumeo, po date ima redova:

Code:

mysql> select date, count(*) from trending_overall group by date;
+------------+----------+
| date       | count(*) |
+------------+----------+
| 1554483586 |  1288240 |
| 1554704740 |  1323969 |
| 1554929743 |  1360810 |
| 1555232040 |  1378657 |
| 1555583066 |  1387371 |
| 1555828179 |  1395854 |
| 1556099122 |  1403179 |
| 1556262087 |  1409580 |
| 1556517637 |  1414902 |
| 1556609140 |  1418715 |
| 1556745152 |  1421668 |
| 1557267052 |  1425467 |
| 1557384345 |  1436756 |
| 1557512132 |  1614814 |
| 1557657514 |  1617573 |
| 1558017570 |  1621903 |
| 1558262317 |  1625581 |
| 1558509128 |  1627067 |
| 1558628616 |  1628844 |
| 1558946249 |  1630404 |
| 1559053406 |  1632242 |
| 1559152236 |  1633373 |
| 1559331550 |  1634352 |
| 1559982601 |  1635168 |
| 1560078807 |  1648915 |
| 1560231247 |  1649771 |
| 1560330282 |  1650512 |
| 1560497291 |  1651288 |
| 1560714791 |  1651978 |
| 1560861338 |  1652632 |
| 1560953763 |  1653155 |
| 1561182374 |  1653780 |
| 1561299782 |  1654920 |
| 1561477511 |  1655482 |
| 1561840485 |  1655889 |
| 1561915435 |  1656294 |
| 1562225838 |  1656992 |
| 1562590029 |  1657820 |
| 1562830216 |  1658323 |
| 1562948513 |  1658622 |
| 1563019036 |  1659062 |
| 1563125852 |  1659341 |
| 1563281748 |  1659593 |
| 1563526054 |  1659898 |
| 1563696206 |  1660037 |
| 1563886734 |  1660234 |
| 1564085446 |  1660551 |
| 1564215027 |  1660773 |
| 1564343823 |  1660866 |
| 1564602871 |  1661124 |
| 1564931639 |  1661390 |
| 1565511453 |  1661426 |
| 1565630689 |  1662021 |
| 1565762493 |  1662108 |
| 1565875686 |  1662310 |
| 1566116107 |  1662573 |
| 1566321203 |  1662864 |
| 1568319989 |  1711773 |
| 1568369921 |  1714713 |
| 1568662153 |  1716665 |
| 1568711391 |  1718406 |
| 1568881693 |  1719459 |
| 1569003906 |  1721054 |
| 1569159895 |  1723289 |
+------------+----------+
64 rows in set (22.72 sec)

[ bogdan.kecman @ 24.09.2019. 22:51 ] @

zipa ne mogu obecam da ce resimo problem i sa datom, samo kazem bez date
je za123 posto nema realnih odgovora u lokalu :D

isctiaj u slobodno vreme
https://dev.mysql.com/doc/refm.../index-merge-optimization.html
https://dev.mysql.com/doc/refm.../en/order-by-optimization.html

ako nije neki proprietary data salji dump ovde, ako jeste mozes i
privatno ..

[ bogdan.kecman @ 24.09.2019. 22:52 ] @

to je ok za dump ako nemas nikakve trigere, procedure etc koji uticu

[ svepomalo @ 24.09.2019. 22:52 ] @

vazi, hocu :)

aj saljem ti privatno, samo da podignem :)

[ Branimir Maksimovic @ 25.09.2019. 03:23 ] @

Citat:

svepomalo:

Citat:

Branimir Maksimovic: Digni indeks nad "overall_review_percentage", tad nece morati da sortira...

pa imam index nad tom kolonom :/

Probaj da dignes kompozitni nad datumom i tom kolonom...

i onda order by date, overall_review_percentage...

[ bogdan.kecman @ 25.09.2019. 16:39 ] @

ovaj moj pateticni VM je celu noc importovo ovih 6G :D .. videcemo popodne na sta lice upiti :D

[ bogdan.kecman @ 25.09.2019. 16:49 ] @

Code:

mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree 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;
ERROR 1146 (42S02): Table 'test.trending_history' doesn't exist
mysql [localhost:8017] {msandbox} (test) >

nisi mi poslao trending_history :D

koji upit optimizujemo?

[ svepomalo @ 25.09.2019. 16:52 ] @

Jaooo nije history, izvini....

trending_overall je tabela :-)

[ svepomalo @ 25.09.2019. 16:55 ] @

valjda zbog primera sam stavio history... i umesto likes overall_review_percentage, ovde ima index :)

ustvari evo celog upita:

Code:

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

izvni na telefonu sam... :/

[ bogdan.kecman @ 25.09.2019. 17:03 ] @

cool, ja sam pustio da pravi ovaj index sto je kolega predlozio, to ce da potraje :D pa cu vidim kako se ponasa :D

[ bogdan.kecman @ 25.09.2019. 17:14 ] @

Code:

mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree SELECT * FROM trending_overall WHERE date = 1568662153 ORDER BY overall_review_percentage DESC;
+--------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: trending_overall.overall_review_percentage DESC
    -> Index lookup on trending_overall using date (date=1568662153)
|
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

mysql [localhost:8017] {msandbox} (test) > alter table trending_overall add key date_overallpc (`date`, `overall_review_percentage`);
Query OK, 0 rows affected (19 min 23.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree SELECT * FROM trending_overall WHERE date = 1568662153 ORDER BY overall_review_percentage DESC;
+------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                        |
+------------------------------------------------------------------------------------------------+
| -> Index lookup on trending_overall using date_overallpc (date=1568662153; iterate backwards)
|
+------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

upit je brz sada sa ovim kompozitom i radi lepo ..

e sad ovaj sto si reko:

Code:

mysql [localhost:8017] {msandbox} (test) > EXPLAIN format=tree 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;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 50 row(s)
    -> Nested loop inner join
        -> Index lookup on trending_overall using date_overallpc (date=1568662153; iterate backwards)
        -> Single-row index lookup on trending using PRIMARY (id=trending_overall.product_id)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

i on izgleda ok

[ bogdan.kecman @ 25.09.2019. 17:15 ] @

Code:

50 rows in set (0.05 sec)

[ svepomalo @ 25.09.2019. 21:43 ] @

evo me, sorry sto kasnim sa odgovorom

super deluje, hvala puno momci :)

sada cu da probam kod mene na live bazi i ovaj 5.7 mysql pa javljam utiske

a inace neko objasnjenje zasto sa composite radi kako treba?

[ svepomalo @ 25.09.2019. 22:58 ] @

hvala puno, ovo radi kako treba i na ovoj bazi koju trenutno imam na live

ovo mi je bila bas kriticna tacka :)

jos nesto da pitam :)

imam taj CSV file i u njemu ASIN kolonu, ovo je string i to je unique za svaki proizvod

dodao sam index za ovo (trending tabela) jer moram da proverim da li proizvod vec postoji u bazi, ako postoji onda ide update a ko ne onda insert
za insert i update koristim batch

e sada ako ne postoji, a imam i history, u ovoj (trending_overall) mi treba i product_id

da ne bih isao kroz petlju jedan po jedan i da hvatam last insert id ja sam odradio ovako
u history ubacim product_id = 0 na pocetku (i ovede je batch insert) pa na kraju odradim da bih popunio product_id i kasnije mogao da spojim ove 2 tabele:

Code:

UPDATE `trending_overall` INNER JOIN `trending` ON `trending`.`asin` = `trending_overall`.`asin` SET `trending_overall`.`product_id` = `trending`.`id` WHERE `trending_overall`.`product_id` = 0;

e sada jel valja ovo ili ne?

sta me muci, da li je mozda bolje da izbacim id iz trending-a (auto increment) i da ostavim samo asin (index)?
sto se tice druge tabele, u trending_history da izbacim producti_id, dodam index nad asin i da ih spajam tako?

mislim da je mozda bolje da ovako uradim, ali me muci ovaj index (asin) koji je varchar a to nije dobro? :/

[ bogdan.kecman @ 26.09.2019. 00:50 ] @

trebaju ti ta dva indexa, mysql tu ne ume da koristi index merge (tj po
dizajnu ne moze) tako da sa kompozitnim moze da koristi index prvo po
prvoj pa onda po drugoj koloni ... prvi deo indexa odradi = a drugi
ostalo ... da imas > onda ti kompozitni ne bi radio

nikako nemoj da izbacujes unique id iz te tabele

[ Living Light @ 26.09.2019. 01:20 ] @

Citat:

bogdan.kecman: inace, u zavisnost za to koliko cesto imas taj deo

Bogdane,
Ništa vas ne razumem, ali Vas i dalje čitam, posmatram i upijam.
Nadam se da se ne ljutite zbog mog javljanja.

Ne bih da vas "deranžiram", samo nastavite tako, pametno.

Bravo, Momci !!!

(@svepomalo,
Izvini za ovaj moj "upad",
nisam mogao da se odlućim kome da se izvinem, tebi ili Bogdanu,
ali mi je Bogdan bliži zbog elektronike)

Svaka Vama Čast !

[ svepomalo @ 26.09.2019. 01:21 ] @

nesto mi nije jasno... imam kompozitni u trending_overall (date,overall_review_percentage) ovaj deo:

Citat:

prvi deo indexa odradi = a drugi
ostalo ... da imas > onda ti kompozitni ne bi radio

ovo sve radi brzo:

Code:

select * from trending_overall where date > 1555583066 and overall_review_percentage > 5 limit 50;

select * from trending_overall where date = 1555583066 and overall_review_percentage > 5 limit 50;

select * from trending_overall where date > 1555583066 and overall_review_percentage > 5 order by overall_review_percentage DESC limit 50

i ovo mi onako maglovito: "nikako nemoj da izbacujes unique id iz te tabele" jel mislis na trending ili trending_overall?

ako mi je asin (ovo je unique) u obe tabele index zar ne bi trebalo da spoji ovo brzo? konkretno na upit:

select .... FROM trending JOIN trending_overall ON trending_overall.asin= trending.asin

hocu da ubrzam sam insert i update i da izbacim ovaj update na kraju

[ svepomalo @ 26.09.2019. 01:27 ] @

@Living Light

nemas potrebe da se izvinjavas (barem meni :D) drago mi da je ova moja muka nekom interesantna i da moze da pomogne :)

[ Living Light @ 26.09.2019. 02:43 ] @

@svepomalo,
Hvala ti !

Ako išta znaš, znaš da pogađaš! :)
-------------------------------------------------------------

Bogdan je prezauzet, ali kad ima vremena ON ne odustaje da pomogne!

I tu pomoć deli širokospktralno, samo onome ko zna sve to da upije/shvati!

Eeeh moj brajko,
da nam je da Bogdan uveče legne na 18 indigoa na krevet,
pa da ujutru ustanu 18 Bogdana.

Takvi "Maheri/Fachmann-i" nam trebaju!

Koji su uzemljeni sa obe noge sa 150mm kvadratnih, Bakarne, fino Licnaste,
i NE lupetaraju gluhhhposti kad usta otvore.

BOGDAN je TAJ !!! Veruj Mi !

pOz

[ bogdan.kecman @ 26.09.2019. 13:08 ] @

nisam obratio paznju sta sve ima od indexa - nemoj nikad da izbacujes PK
iz tabele, ako taj asin moze da bude PK onda ok

[ bogdan.kecman @ 26.09.2019. 13:25 ] @

@llight, branimir je ovde resio problem jos pre nego sam ja pogledao
problem od pocetka do kraja :D