Abfrage dauert zu lange

long_forum

Benutzer
Beiträge
17
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???
 
Werbung:
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)
 
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?
 
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.
 
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?
 
Ich benutze halt MySQL - würde ungern auf PostgreSQL oder eine andere Datenbank wechseln. Mit EXPLAIN dauert es auch genauso lange.
 
Und wie komme ich jetzt weiter mit meinem Problem???

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.
 
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 |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
 
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.
 
Werbung:
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.

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???
 
Zurück
Oben