MySQL Abfrage dauert 7 Sekunden

[long_forum]

Mahlzeit,

habe einen Fulltext Index auf 3 Spalten anlegt:
Code:

ALTER TABLE SystemEvents ADD FULLTEXT count_entrys(FromHost, SysLogTag, Message(500));

Aber leider benötigt er 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 '%';

Ziel ist es, möglichst schnell die Anzahl der Einträge ohne und mit Filter zu zählen1

[h3ll]

Wozu die unnötigen LIKE?

[long_forum]

Ist aus aus den Anfängen meiner Abfrage. Habe jetzt bloß leider festgestellt, dass die Abfrage zu lange dauert, egal ob mit oder ohne Index.



Bin für jede Lösung dankbar.

[h3ll]

Naja, dein LIKE umgeht die Verwendung von einem Index. Wundert mich nicht, dass das langsam ist.

[long_forum]

Aber müsste nicht der Befehl

SELECT COUNT(*) FROM SystemEvents WHERE MATCH(FromHost, SysLogTag, Message) AGAINST('192.168.*' IN NATURAL LANGUAGE MODE);

schneller gehen? Er benötigt aber die gleiche Zeit!

[h3ll]

Hast du schon geschaut, was das EXPLAIN sagt?

[long_forum]

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

Mit Explain:

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

[h3ll]

Wie bereits gesagt, mit LIKE verhinderst du die Nutzung eines Index und die Abfrage dauerst wesentlich länger. Warum ist das jetzt immer noch drin?

[long_forum]

Wie schreibe ich es den um bzw. anders? Mit Match bekomme ich auch keine richtige Trefferanzahl bzw. es dauert zu lange?

[h3ll]

Wie schaut das EXPLAIN dazu aus?

[long_forum]

EXPLAIN SELECT COUNT(*) FROM SystemEvents WHERE MATCH(FromHost, SysLogTag, Message) AGAINST('%' IN NATURAL LANGUAGE MODE)"

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

[h3ll]

Ein AGAINST auf '%' macht überhaupt keinen Sinn.

Hast du eigentlich irgendeine Ahnung davon, was du da tust? Für mich wirkt das alles zufallsmäßig zusammengewürfelt, ohne sich auch nur irgendwelche Gedanken gemacht oder Doku gelesen zu haben.

[long_forum]

Das ist meine Problem, dass ich die Dokus nicht richtig verstehe bzw. immer weiter aufeinander programmiert habe und es deshalb so zusammengewürfelt ist. Ich hätte wahrscheinlich auch nie Indexe benutzt, wenn ich nicht diese auf das Zeitproblem gestoßen wäre. Dann habe ich auf eine Spalte ein Index gelegt, welcher gut funktioniert hat aber bei der Abfrage von 3 und mehr Spalten eben nicht mehr.



Ich werde gleich mal meine Tabellenstruktur posten sowie was ich abfragen möchte. Also von Anfang an.

[long_forum]

Ich fange mal ganz von vorn an. Die empfangenden Meldungen von rsyslog werden in eine MySQL DB gespeicht. Ziel ist es mittels PHP diese anzeigen zu lassen. Ich erwarte pro Tag ca. 1 Million Meldungen und möchte diese 30 Tage vorhalten - also ca. 30 Millionen Einträge.

DESCRIBE SystemEvents;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| ID                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| CustomerID         | bigint(20)       | YES  |     | NULL    |                |
| ReceivedAt         | datetime         | YES  |     | NULL    |                |
| DeviceReportedTime | datetime         | YES  | MUL | NULL    |                |
| Facility           | smallint(6)      | YES  |     | NULL    |                |
| Priority           | smallint(6)      | YES  |     | NULL    |                |
| FromHost           | varchar(60)      | YES  | MUL | NULL    |                |
| Message            | text             | YES  | MUL | NULL    |                |
| NTSeverity         | int(11)          | YES  |     | NULL    |                |
| Importance         | int(11)          | YES  |     | NULL    |                |
| EventSource        | varchar(60)      | YES  |     | NULL    |                |
| EventUser          | varchar(60)      | YES  |     | NULL    |                |
| EventCategory      | int(11)          | YES  |     | NULL    |                |
| EventID            | int(11)          | YES  |     | NULL    |                |
| EventBinaryData    | text             | YES  |     | NULL    |                |
| MaxAvailable       | int(11)          | YES  |     | NULL    |                |
| CurrUsage          | int(11)          | YES  |     | NULL    |                |
| MinUsage           | int(11)          | YES  |     | NULL    |                |
| MaxUsage           | int(11)          | YES  |     | NULL    |                |
| InfoUnitID         | int(11)          | YES  |     | NULL    |                |
| SysLogTag          | varchar(60)      | YES  | MUL | NULL    |                |
| EventLogType       | varchar(60)      | YES  |     | NULL    |                |
| GenericFileName    | varchar(60)      | YES  |     | NULL    |                |
| SystemID           | int(11)          | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

Was habe ich vor:

1.) Ich würde gerne nach den Einträgen in den Spalten FromHost, SysLogTag filtern mittels DISTINCT
2.) Ich würde gerne alle Einträge zählen, welche bestimmte Werte in den Spalten FromHost, SysLogTag, Message, DeviceReportedTime enthalten - Filter können auch leer sein (also alle vorhanden Einträge zählen)
3.) Ausgabe der ersten 1000 Einträge der gefundenen Ergebnisse


Die Frage lautet jetzt, wie muss ich meine Indexe(oder ein anderes Verfahren) anlegen/umsetzen, damit ich die gewünschten Informationen möglichst schnell erhalte???

[h3ll]

Um zu wissen, wie die Indexe aussehen müssen, muss man erstmal die Abfragen kennen. Also solltet du erstmal eine sinnvolle Beispielabfrage zeigen.

Außerdem solltest du die Tabellenstruktur mit wenigen Beispieldaten als SQL-Code posten, damit man das bei sich selber nachstellen kann.