Drukowana wersja tematu

Kliknij tu, aby zobaczyć temat w orginalnym formacie

Forum PHP.pl _ MySQL _ [mysql] count, group by, order by

Napisany przez: pionas 2.02.2015, 15:10:13

Cześć,

mam takie zapytanie:

[SQL] pobierz, plaintext 
  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;
[SQL] pobierz, plaintext


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

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

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

jest ponad 48 000 rekordów

[SQL] pobierz, plaintext 
  1. SELECT * FROM articles
[SQL] pobierz, plaintext

ponad 55 000 rekordów

[SQL] pobierz, plaintext 
  1. SELECT * FROM articles_brkon
[SQL] pobierz, plaintext

ponad 4 500 000 rekordów

[SQL] pobierz, plaintext 
  1. SELECT * FROM articles_brkon WHERE typ=1
[SQL] pobierz, plaintext

prawie 1 000 000 rekordów

Z góry dziękuję za pomoc

[EDIT]
Czy może rozbić to zapytanie na dwa:
[SQL] pobierz, plaintext 
  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;
[SQL] pobierz, plaintext


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



[EDIT2]
dopiero dojrzałem że order by idzie asc, nie ma możliwości ustawienia desc.
[SQL] pobierz, plaintext 
  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( ) ))) 
[SQL] pobierz, plaintext


zamienić powinienem na:
[SQL] pobierz, plaintext 
  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 
[SQL] pobierz, plaintext

Napisany przez: sazian 2.02.2015, 21:30:54

1)formatuj zapytania. Użycie entera naprawdę nie boli, a czytelność poprawia znacząco
2)pokaz wynik EXPLAIN

Napisany przez: mmmmmmm 2.02.2015, 23:05:05

A po co ci LEFT JOIN ? On psuje...

Napisany przez: pionas 3.02.2015, 06:24:11 

[SQL] pobierz, plaintext 
  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
[SQL] pobierz, plaintext


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

Napisany przez: sazian 3.02.2015, 19:37:31

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

Napisany przez: pionas 3.02.2015, 21:16:16

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ę

[SQL] pobierz, plaintext 
  1.  SELECT articles_id, count(*) udzial FROM articles_brkon WHERE articles_id IN ({$id}) AND typ =1 GROUP BY articles_id
[SQL] pobierz, plaintext


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

Napisany przez: sazian 3.02.2015, 21:21:36

a gdyby przenieść tego counta do podzapytania ?

Napisany przez: pionas 5.02.2015, 06:35:32

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:
[SQL] pobierz, plaintext 
  1. EXPLAIN SELECT count(*) total FROM forum_posts p WHERE p.post_deleted=0 AND p.post_message LIKE '%ABC%'
[SQL] pobierz, plaintext

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

Napisany przez: mmmmmmm 5.02.2015, 07:11:31

Czemu nie chce dać indeksu?
Bo masz LIKE. Zastosuj funkcje do FULLTEXTa, to użyje..

Napisany przez: Pyton_000 5.02.2015, 08:32:29

Jeżeli być miał LIKE 'ABC%' to by zadziałał, ale tak jak @mmmmmmm napisał użyj funkcji która używa fulltext index

Napisany przez: pionas 5.02.2015, 15:34:04

Dałem:

[SQL] pobierz, plaintext 
  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
[SQL] pobierz, plaintext


czemu tak?

Napisany przez: sazian 5.02.2015, 20:16:41

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

coś w ten deseń 
[SQL] pobierz, plaintext 
  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.  
[SQL] pobierz, plaintext


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


Napisany przez: pionas 5.02.2015, 20:48:13

Cytat(sazian @ 5.02.2015, 20:16:41 ) *
coś w ten deseń 
[SQL] pobierz, plaintext 
  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.  
[SQL] pobierz, plaintext


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



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...

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)