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

Vise where nad velikom tabelom

[es] :: MySQL :: Vise where nad velikom tabelom

Strane: 1 2 3

[ Pregleda: 5375 | Odgovora: 50 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom20.09.2019. u 00:47 - pre 55 meseci
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
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom20.09.2019. u 01:04 - pre 55 meseci
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:
Code:
+----+-------------+------------------+------------+--------+-----------------+---------+---------+----------------------------------------------+---------+----------+-------------+
| id | select_type | table            | partitions | type   | possible_keys   | key     | key_len | ref                                          | rows    | filtered | Extra       |
+----+-------------+------------------+------------+--------+-----------------+---------+---------+----------------------------------------------+---------+----------+-------------+
|  1 | SIMPLE      | trending_overall | NULL       | ref    | product_id,date | date    | 8       | const                                        | 3338582 |   100.00 | NULL        |
|  1 | SIMPLE      | trending         | NULL       | eq_ref | PRIMARY         | PRIMARY | 8       | admin_competitor.trending_overall.product_id |       1 |   100.00 | Using index |
+----+-------------+------------------+------------+--------+-----------------+---------+---------+----------------------------------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


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

Code:
| id | select_type | table            | partitions | type   | possible_keys | key                       | key_len | ref                                          | rows | filtered | Extra |
+----+-------------+------------------+------------+--------+---------------+---------------------------+---------+----------------------------------------------+------+----------+-------+
|  1 | SIMPLE      | trending_overall | NULL       | index  | product_id    | overall_review_percentage | 5       | NULL                                         |   50 |   100.00 | NULL  |
|  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)


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

Code:
+----+-------------+------------------+------------+--------+-----------------+---------------------------+---------+----------------------------------------------+------+----------+-------------+
| 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                                         | 1407 |     3.55 | 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)


jel date index treba da se isforsira?
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom20.09.2019. u 01:11 - pre 55 meseci
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:

Code:

+----+-------------+------------------+------------+--------+---------------+---------+---------+----------------------------------------------+---------+----------+---------------------------------------+
| id | select_type | table            | partitions | type   | possible_keys | key     | key_len | ref                                          | rows    | filtered | Extra                                 |
+----+-------------+------------------+------------+--------+---------------+---------+---------+----------------------------------------------+---------+----------+---------------------------------------+
|  1 | SIMPLE      | trending_overall | NULL       | ref    | date          | date    | 8       | const                                        | 3338582 |   100.00 | Using index condition; Using filesort |
|  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)

 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom20.09.2019. u 01:37 - pre 55 meseci
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]
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom22.09.2019. u 21:42 - pre 54 meseci
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

Code:

+----+-------------+------------------+------------+------+---------------+------+---------+-------+---------+----------+-------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref   | rows    | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+-------+---------+----------+-------+
|  1 | SIMPLE      | trending_overall | NULL       | ref  | date          | date | 8       | const | 3338582 |   100.00 | NULL  |
+----+-------------+------------------+------------+------+---------------+------+---------+-------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)



analyze kaze:
Code:

mysql> ANALYZE TABLE trending_overall;
+-----------------------------------+---------+----------+----------+
| Table                             | Op      | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| trending_overall | analyze | status   | OK       |
+-----------------------------------+---------+----------+----------+
1 row in set (0.10 sec)

mysql> ANALYZE TABLE trending;
+---------------------------+---------+----------+----------+
| Table                     | Op      | Msg_type | Msg_text |
+---------------------------+---------+----------+----------+
| trending | analyze | status   | OK       |
+---------------------------+---------+----------+----------+
1 row in set (0.03 sec)



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
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom22.09.2019. u 21:50 - pre 54 meseci
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
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 18:04 - pre 54 meseci
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:

Code:

mysql> EXPLAIN SELECT * FROM trending_overall WHERE date = 1568662153 ORDER BY overall_review_percentage DESC;
+----+-------------+------------------+------------+------+---------------+------+---------+-------+---------+----------+---------------------------------------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref   | rows    | filtered | Extra                                 |
+----+-------------+------------------+------------+------+---------------+------+---------+-------+---------+----------+---------------------------------------+
|  1 | SIMPLE      | trending_overall | NULL       | ref  | date          | date | 8       | const | 3338582 |   100.00 | Using index condition; Using filesort |
+----+-------------+------------------+------------+------+---------------+------+---------+-------+---------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)



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
 
Odgovor na temu

Branimir Maksimovic

Član broj: 64947
Poruke: 5534
109.72.51.23



+1064 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 19:32 - pre 54 meseci
Digni indeks nad "overall_review_percentage", tad nece morati da sortira...
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 22:06 - pre 54 meseci
Citat:
Branimir Maksimovic: Digni indeks nad "overall_review_percentage", tad nece morati da sortira...


pa imam index nad tom kolonom :/
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 22:33 - pre 54 meseci
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
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 22:49 - pre 54 meseci
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)


 
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: Vise where nad velikom tabelom24.09.2019. u 22:51 - pre 54 meseci
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 ..
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 22:52 - pre 54 meseci
to je ok za dump ako nemas nikakve trigere, procedure etc koji uticu

 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom24.09.2019. u 22:52 - pre 54 meseci
vazi, hocu :)

aj saljem ti privatno, samo da podignem :)
 
Odgovor na temu

Branimir Maksimovic

Član broj: 64947
Poruke: 5534
109.72.51.23



+1064 Profil

icon Re: Vise where nad velikom tabelom25.09.2019. u 03:23 - pre 54 meseci
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...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom25.09.2019. u 16:39 - pre 54 meseci
ovaj moj pateticni VM je celu noc importovo ovih 6G :D .. videcemo popodne na sta lice upiti :D

 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom25.09.2019. u 16:49 - pre 54 meseci
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?
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom25.09.2019. u 16:52 - pre 54 meseci
Jaooo nije history, izvini....

trending_overall je tabela :-)
 
Odgovor na temu

svepomalo

Član broj: 306404
Poruke: 196



+21 Profil

icon Re: Vise where nad velikom tabelom25.09.2019. u 16:55 - pre 54 meseci
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... :/
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Vise where nad velikom tabelom25.09.2019. u 17:03 - pre 54 meseci
cool, ja sam pustio da pravi ovaj index sto je kolega predlozio, to ce da potraje :D pa cu vidim kako se ponasa :D
 
Odgovor na temu

[es] :: MySQL :: Vise where nad velikom tabelom

Strane: 1 2 3

[ Pregleda: 5375 | Odgovora: 50 ] > FB > Twit

Postavi temu Odgovori

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