1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Abfrage dauert zu lange

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von long_forum, 20 Mai 2020.

  1. long_forum

    long_forum Benutzer

    Morgen,

    ich habe die nachfolgende Abfrage, welche auf ca. 2 Millionen Datensätze aus rsyslog zugreift. Ich würde jetzt gerne die Datensätze zählen lassen - entweder alle Datensätze oder je nach Suchoption mittels like. Leider braucht die Abfrage 6 Sekunden und mehr ....

    Code:
    SELECT COUNT(*) FROM SystemEvents WHERE FromHost like '%' AND SysLogTag like '%' AND Message like '%' AND DeviceReportedTime like '%';
    Selbst mit Unterabfragen benötigt er sogar noch mehr Zeit:

    Code:
    SELECT COUNT(*) FROM
    (SELECT FromHost FROM
    (SELECT FromHost, SysLogTag FROM
    (SELECT FromHost, SysLogTag, Message FROM
    (SELECT FromHost, SyslogTag, DeviceReportedTime, Message FROM SystemEvents WHERE DeviceReportedTime like '%".$txt_DeviceReportedTime."%') tab1
    WHERE Message like '%".$txt_Message."%') tab2
    WHERE SysLogTag like '%".$search_SysLogTag."%') tab3
    WHERE FromHost like '%".$search_FromHost."%') tab4 
    Habe auf die Felder FromHost, DeviceReportedTime, SysLogTag und Message einen index gesetzt nach dem Schema:
    Code:
    ALTER TABLE SystemEvents ADD INDEX (columname);
    Wie kann ich meine Abfrage beschleunigungen bzw. meinen Indexe sinnvoll dafür einbinden???
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Dafür würdest Du wohl andere Indexe für eine Fulltext-Suche benötigen, in PG würde man da ts_vector - Spalte einrichten, etwa so:

    Code:
    test=*# create table fts (id bigint generated always as identity, t text, tsv tsvector generated always as (to_tsvector('english',t)) stored);
    CREATE TABLE
    test=*# create index idx_fts on fts using gin(tsv);
    CREATE INDEX
    test=*# explain analyse select * from fts where tsv @@ '%bla';
                                                       QUERY PLAN                                                   
    ----------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on fts  (cost=8.03..16.49 rows=4 width=72) (actual time=0.007..0.007 rows=0 loops=1)
       Recheck Cond: (tsv @@ '''%bla'''::tsquery)
       ->  Bitmap Index Scan on idx_fts  (cost=0.00..8.03 rows=4 width=0) (actual time=0.006..0.006 rows=0 loops=1)
             Index Cond: (tsv @@ '''%bla'''::tsquery)
     Planning Time: 0.125 ms
     Execution Time: 0.074 ms
    (6 rows)
    
     
  3. long_forum

    long_forum Benutzer

    Tut mir leid aber das ist mir zu hoch ... ich glaube wir müssten mit der Erklärung tiefer stapeln bzw. an meinem Bsp. durchspielen!

    Wäre das möglich?
     
  4. Walter

    Walter Administrator Mitarbeiter

  5. akretschmer

    akretschmer Datenbank-Guru

    Nun, ich habe eine Tabelle erstellt (create table), einen Index (create index) und via explain geschaut, ob der Index genutzt wird. Wie man sieht, ist dies der Fall (okay, ich habe noch 'set enable_seqscan to off' gesetzt, weil die Tabelle ja leer ist).

    Allerdings verwende ich kein MySQL.
     
  6. long_forum

    long_forum Benutzer

    Morgen,

    habe jetzt mal einen Fulltext Index auf meine 3 Spalten gelegt:
    Code:
    ALTER TABLE SystemEvents ADD FULLTEXT count_entrys(FromHost, SysLogTag, Message(500));
    Aber leider benötigt er immer noch 7 Sekunden für die Abfrage bei 2,8 Millionen Einträgen egal welche Abfrage ich verwende:
    Code:
    SELECT COUNT(*) FROM SystemEvents WHERE MATCH(FromHost, SysLogTag, Message) AGAINST('192.168.*' IN NATURAL LANGUAGE MODE);
    SELECT COUNT(*) FROM SystemEvents WHERE FromHost like '%' AND SysLogTag like '%' AND Message like '%';
    SELECT COUNT(FromHost) FROM SystemEvents WHERE FromHost like '%' AND SysLogTag like '%' AND Message like '%';
    
    Was mache ich den falsch?
     
  7. akretschmer

    akretschmer Datenbank-Guru

    • du verwendest kein EXPLAIN
    • du verwendest MySQL
     
  8. long_forum

    long_forum Benutzer

    Ich benutze halt MySQL - würde ungern auf PostgreSQL oder eine andere Datenbank wechseln. Mit EXPLAIN dauert es auch genauso lange.
     
  9. akretschmer

    akretschmer Datenbank-Guru

    LOL. Explain dient nicht dazu, eine Abfrage zu beschleunigen.
     
  10. long_forum

    long_forum Benutzer

    Und wie komme ich jetzt weiter mit meinem Problem???
     
  11. akretschmer

    akretschmer Datenbank-Guru

    Du könntest schauen, ob die Indexe genutzt werden können für Deine Abfrage. Mal als Beispiel: (quick&dirty Schnellschuß)

    Code:
    test=# \d+ fts
                                                                     Table "public.fts"
     Column |   Type   | Collation | Nullable |                             Default                              | Storage  | Stats target | Description
    --------+----------+-----------+----------+------------------------------------------------------------------+----------+--------------+-------------
     id     | integer  |           | not null | nextval('fts_id_seq'::regclass)                                  | plain    |              |
     t      | text     |           |          |                                                                  | extended |              |
     search | tsvector |           |          | generated always as (to_tsvector('german'::regconfig, t)) stored | extended |              |
    Indexes:
        "fts_pkey" PRIMARY KEY, btree (id)
        "tsv_idx_gist" gist (search)
    Access method: heap
    
    In die Spalte t habe ich 84.000 Rows, aus einem PostgreSQL-Logfile eines Kunden:

    Code:
    test=# select count(1) from fts;
     count
    -------
     84019
    (1 row)
    
    
    Nun will ich wissen, wie oft '%blafasel' vorkommt, dazu nutze ich EXPLAIN ANALYSE, welches die Abfrage komplett ausführt und mir Informationen über Index-Nutzung und Timing gibt:

    Code:
    test=*# explain (analyse,buffers) select * from fts where search @@ '%blafasel';
                                                           QUERY PLAN                                                       
    ------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on fts  (cost=17.15..429.62 rows=112 width=835) (actual time=1.532..1.532 rows=0 loops=1)
       Recheck Cond: (search @@ '''%blafasel'''::tsquery)
       Rows Removed by Index Recheck: 8
       Heap Blocks: exact=8
       Buffers: shared hit=330
       ->  Bitmap Index Scan on tsv_idx_gist  (cost=0.00..17.12 rows=112 width=0) (actual time=1.436..1.436 rows=8 loops=1)
             Index Cond: (search @@ '''%blafasel'''::tsquery)
             Buffers: shared hit=298
     Planning Time: 0.229 ms
     Execution Time: 1.608 ms
    (10 rows)
    
    Laufzeit also 1,6 Millisekunden. Selbst wenn die DB jetzt 25 mal größer wäre wie Deine, würde die Laufzeit um deutlich geringer als das 25fache ansteigen. Und selbst wenn ...


    PS.: das Explain von MySQL ist, vorsichtig formuliert, etwas weniger aussagekräftig.
     
  12. long_forum

    long_forum Benutzer

    Irgendetwas stimmt nicht ... wenn ich ohne Explain Abfrage sind es ca. 230T Einträge. Der Wert stimmt!

    Mit Explain:

    Code:
    EXPLAIN SELECT COUNT(*) FROM SystemEvents WHERE FromHost like '%%' AND SysLogTag like '%testentry%' AND Message like '%%'
    +----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | SystemEvents | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2653309 |     0.14 | Using where |
    +----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
     
  13. akretschmer

    akretschmer Datenbank-Guru

    er nutzt halt offenbar keinen Index.
     
  14. BerndB

    BerndB Datenbank-Guru

    Nur kurz zur Info: LIKE mit % am Anfang wie:

    SELECT * FROM yourTable WHERE fieldx LIKE '%findtext';

    ist IMMER ein FULL TABLE SCAN und kann keinen INDEX nutzen.
     
  15. long_forum

    long_forum Benutzer

    Wie schaffe ich es aber dann die Einträge nach bestimmten Suchkriterien (SysLogTag, FromHost, Message) zu zählen, ob mit oder ohne Index in einer ansprechend schnellen Zeit???
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden