Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> [mysql] count, group by, order by, wykonuje się ponad 15s :/
pionas
post 2.02.2015, 15:10:13
Post #1





Grupa: Zarejestrowani
Postów: 70
Pomógł: 2
Dołączył: 25.03.2009
Skąd: Pionki

Ostrzeżenie: (0%)
-----


Cześć,

mam takie zapytanie:
  1. SELECT a.*,IF (a.date_end = '0000-00-00', 9999998, IF (TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) < 0, 9999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ))) toend, count(b.articles_id) udzial FROM articles a LEFT JOIN articles_brkon b ON (b.typ=1 AND a.id=b.articles_id) WHERE (TO_DAYS( curdate( ) ) -TO_DAYS( a.date_end )>=120) AND cid IN (2,4) AND a.st=1 GROUP BY a.id ORDER BY IF (a.date_end = '0000-00-00', 999998,IF (TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) < 0, 999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ))) LIMIT 39370, 10;


niestety wykonuje się ono bardzo długo (>15s)

Ktoś może pomóc jak to zoptymalizować?

do zapytania
  1. SELECT * FROM articles WHERE (TO_DAYS( curdate( ) ) -TO_DAYS( date_end )>=120) AND cid IN (2,4) AND st=1

jest ponad 48 000 rekordów

  1. SELECT * FROM articles

ponad 55 000 rekordów

  1. SELECT * FROM articles_brkon

ponad 4 500 000 rekordów

  1. SELECT * FROM articles_brkon WHERE typ=1

prawie 1 000 000 rekordów

Z góry dziękuję za pomoc

[EDIT]
Czy może rozbić to zapytanie na dwa:
  1. SELECT a.*,IF (a.date_end = '0000-00-00', 9999998, IF (TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) < 0, 9999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ))) toend FROM articles a WHERE (TO_DAYS( curdate( ) ) -TO_DAYS( a.date_end )>=120) AND cid IN (2,4) AND a.st=1 ORDER BY IF (a.date_end = '0000-00-00', 999998,IF (TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) < 0, 999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ))) LIMIT 39370, 10;


pobrać id i:
  1. SELECT articles_id, count(*) FROM articles_brkon WHERE typ=1 AND articles_id IN ({rekordy}) GROUP BY articles_id



[EDIT2]
dopiero dojrzałem że order by idzie asc, nie ma możliwości ustawienia desc.
  1. ORDER BY IF (a.date_end = '0000-00-00', 999998,IF (TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) < 0, 999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) )))


zamienić powinienem na:
  1. ORDER BY
  2. CASE `date_end` WHEN a.date_end = '0000-00-00' THEN 999998 WHEN TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) <0 THEN 999999 ELSE TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) END ASC


Ten post edytował pionas 2.02.2015, 15:41:35


--------------------
Organizujesz konkurs? Chcesz coś wygrać? Wejdź na www.e-Konkursy.info :)
Go to the top of the page
+Quote Post
sazian
post 2.02.2015, 21:30:54
Post #2





Grupa: Zarejestrowani
Postów: 1 043
Pomógł: 141
Dołączył: 19.09.2006
Skąd: B-tów

Ostrzeżenie: (0%)
-----


1)formatuj zapytania. Użycie entera naprawdę nie boli, a czytelność poprawia znacząco
2)pokaz wynik EXPLAIN
Go to the top of the page
+Quote Post
mmmmmmm
post 2.02.2015, 23:05:05
Post #3





Grupa: Zarejestrowani
Postów: 1 421
Pomógł: 310
Dołączył: 18.04.2012

Ostrzeżenie: (0%)
-----


A po co ci LEFT JOIN ? On psuje...
Go to the top of the page
+Quote Post
pionas
post 3.02.2015, 06:24:11
Post #4





Grupa: Zarejestrowani
Postów: 70
Pomógł: 2
Dołączył: 25.03.2009
Skąd: Pionki

Ostrzeżenie: (0%)
-----


  1. EXPLAIN SELECT a . * , IF( a.date_end = '0000-00-00', 9999998, IF( TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) <0, 9999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) ) ) toend, count( b.articles_id ) udzial
  2. FROM articles a
  3. LEFT JOIN articles_brkon b ON ( b.typ =1
  4. AND a.id = b.articles_id )
  5. WHERE (
  6. TO_DAYS( curdate( ) ) - TO_DAYS( a.date_end ) >=120
  7. )
  8. AND cid
  9. IN ( 2, 4 )
  10. AND a.st =1
  11. GROUP BY a.id
  12. ORDER BY IF( a.date_end = '0000-00-00', 999998, IF( TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) <0, 999999, TO_DAYS( a.date_end ) - TO_DAYS( curdate( ) ) ) )
  13. LIMIT 39370 , 10


Kod
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     a     ALL     NULL    NULL    NULL    NULL    54927     Using where; Using temporary; Using filesort
1     SIMPLE     b     ref     articles_id     articles_id     5     konkursy_db2.a.id,const     33     Using index


dodanie indeksu na cid daje:
Kod
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     a     range     cid     cid     1     NULL    53294     Using where; Using temporary; Using filesort
1     SIMPLE     b     ref     articles_id     articles_id     5     konkursy_db2.a.id,const     33     Using index


--------------------
Organizujesz konkurs? Chcesz coś wygrać? Wejdź na www.e-Konkursy.info :)
Go to the top of the page
+Quote Post
sazian
post 3.02.2015, 19:37:31
Post #5





Grupa: Zarejestrowani
Postów: 1 043
Pomógł: 141
Dołączył: 19.09.2006
Skąd: B-tów

Ostrzeżenie: (0%)
-----


spróbuj dać indeks na a.st lub wspólny na cid i a.st jeśli to ta sama tabela

możesz też spróbować zamienić kolejnością warunki
a.st and cid TO_DAYS

ile rekordów jest faktycznie wyświetlanych ? to znaczy ile spełnia warunki jeśli pominie się limit
Go to the top of the page
+Quote Post
pionas
post 3.02.2015, 21:16:16
Post #6





Grupa: Zarejestrowani
Postów: 70
Pomógł: 2
Dołączył: 25.03.2009
Skąd: Pionki

Ostrzeżenie: (0%)
-----


Do wyświetlenia jest 48,511 rekordów...

jeśli pominę count to zapytanie trwa ułamki sekundy, ale wtedy wyświetla 827033 rekordów, gdy dam SELECT DISTINCT bez count to zapytanie trwa ok 1.5s no ale nie zlicza ile osób przeczytało artykuł...

Rozbiłem to na dwa zapytania pobieram wszystkie informacje poza count, pętlą pobieram same articles_id i robię
  1. SELECT articles_id, count(*) udzial FROM articles_brkon WHERE articles_id IN ({$id}) AND typ =1 GROUP BY articles_id


i czas generowania tego wynosi 0,02s...


--------------------
Organizujesz konkurs? Chcesz coś wygrać? Wejdź na www.e-Konkursy.info :)
Go to the top of the page
+Quote Post
sazian
post 3.02.2015, 21:21:36
Post #7





Grupa: Zarejestrowani
Postów: 1 043
Pomógł: 141
Dołączył: 19.09.2006
Skąd: B-tów

Ostrzeżenie: (0%)
-----


a gdyby przenieść tego counta do podzapytania ?
Go to the top of the page
+Quote Post
pionas
post 5.02.2015, 06:35:32
Post #8





Grupa: Zarejestrowani
Postów: 70
Pomógł: 2
Dołączył: 25.03.2009
Skąd: Pionki

Ostrzeżenie: (0%)
-----


Cytat(sazian @ 3.02.2015, 21:21:36 ) *
a gdyby przenieść tego counta do podzapytania ?


tzn? jakiś przykład? wink.gif


mam jeszcze jeden problem:
  1. EXPLAIN SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND p.post_message LIKE '%ABC%'

Kod
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     p     ALL     NULL    NULL    NULL    NULL    1100625     Using where


Indeksy:
PRIMARY -> BTREE post_id
thread_id -> BTREE thread_id
user_id -> BTREE user_id
post_deleted -> BTREE (thread_id, post_deleted)
post_message -> FULLTEXT post_message

czemu nie chce dać indeksu?

USE INDEX (post_message) nie działa :/


--------------------
Organizujesz konkurs? Chcesz coś wygrać? Wejdź na www.e-Konkursy.info :)
Go to the top of the page
+Quote Post
mmmmmmm
post 5.02.2015, 07:11:31
Post #9





Grupa: Zarejestrowani
Postów: 1 421
Pomógł: 310
Dołączył: 18.04.2012

Ostrzeżenie: (0%)
-----


Czemu nie chce dać indeksu?
Bo masz LIKE. Zastosuj funkcje do FULLTEXTa, to użyje..
Go to the top of the page
+Quote Post
Pyton_000
post 5.02.2015, 08:32:29
Post #10





Grupa: Zarejestrowani
Postów: 8 068
Pomógł: 1414
Dołączył: 26.10.2005

Ostrzeżenie: (0%)
-----


Jeżeli być miał LIKE 'ABC%' to by zadziałał, ale tak jak @mmmmmmm napisał użyj funkcji która używa fulltext index
Go to the top of the page
+Quote Post
pionas
post 5.02.2015, 15:34:04
Post #11





Grupa: Zarejestrowani
Postów: 70
Pomógł: 2
Dołączył: 25.03.2009
Skąd: Pionki

Ostrzeżenie: (0%)
-----


Dałem:
  1. SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND MATCH (p.post_message) AGAINST ('abc' IN BOOLEAN MODE) // wynik 0
  2. SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND MATCH (p.post_message) AGAINST ('*abc*' IN BOOLEAN MODE) // wynik 136
  3. SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND MATCH (p.post_message) AGAINST ('abc') // wynik 0
  4. SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND MATCH (p.post_message) AGAINST ('*abc*') // wynik 0
  5. SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND p.post_message LIKE '%abc%' // wynik 11255


czemu tak?


--------------------
Organizujesz konkurs? Chcesz coś wygrać? Wejdź na www.e-Konkursy.info :)
Go to the top of the page
+Quote Post
sazian
post 5.02.2015, 20:16:41
Post #12





Grupa: Zarejestrowani
Postów: 1 043
Pomógł: 141
Dołączył: 19.09.2006
Skąd: B-tów

Ostrzeżenie: (0%)
-----


Cytat(pionas @ 5.02.2015, 06:35:32 ) *
tzn? jakiś przykład? wink.gif

coś w ten deseń
  1. SELECT a.*
  2. , (
  3. SELECT count(*)
  4. FROM articles_brkon b
  5. WHERE b.typ =1
  6. AND a.id = b.articles_id
  7. ) AS udzial
  8.  
  9. FROM articles a
  10.  


a jeśli chodzi o wyszukiwanie to spróbuj tak
  1. MATCH (p.post_message) AGAINST ('+abc*' IN BOOLEAN MODE)
  2.  

Go to the top of the page
+Quote Post
pionas
post 5.02.2015, 20:48:13
Post #13





Grupa: Zarejestrowani
Postów: 70
Pomógł: 2
Dołączył: 25.03.2009
Skąd: Pionki

Ostrzeżenie: (0%)
-----


Cytat(sazian @ 5.02.2015, 20:16:41 ) *
coś w ten deseń
  1. SELECT a.*
  2. , (
  3. SELECT count(*)
  4. FROM articles_brkon b
  5. WHERE b.typ =1
  6. AND a.id = b.articles_id
  7. ) AS udzial
  8.  
  9. FROM articles a
  10.  


a jeśli chodzi o wyszukiwanie to spróbuj tak
  1. MATCH (p.post_message) AGAINST ('+abc*' IN BOOLEAN MODE)
  2.  



1. dzięki, zapomniałem całkiem o czymś takim wink.gif


2. nie działa, tzn. działa, ale też 136 wyników pokazuje...


--------------------
Organizujesz konkurs? Chcesz coś wygrać? Wejdź na www.e-Konkursy.info :)
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 28.03.2024 - 18:11