SELECT DISTINCT(...) beschleunigen (Datenbanken und Verzeichnisdienste)

[thread]10656[/thread]

SELECT DISTINCT(...) beschleunigen

moritz

2007-10-24 23:45

User since
2007-05-11
923 Artikel
HausmeisterIn

Ich habe eine Tabelle in einer Mysql(5.0)-DB, in der ich IRC-Logs speichere, und in jeder Zeile ist neben Text, nick, timestamp etc. auch der Tag als String gespeichert (weil ich die Logs tageweise darstelle) und der channel.

Wenn ich jetzt ein

Code: (dl )

SELECT DISTINCT(channel) FROM irclog;

oder

Code: (dl )

SELECT DISTINCT(day) FROM irclog;

mache, dauert das ziemlich lange (so 5..10 Sekunden), bei ca. 1Mio Spalten und <10 verschiedenen Channels und ~ 1k verschiedenen Tagen.

Ein Index über channel bzw. day hilft da auch kaum etwas.

Meine nächste Idee war ein View, der nur die verschiedenen Werte speichert:

Code: (dl )

CREATE VIEW irclog_channels AS SELECT DISTINCT(channel) FROM irclog;

Dummerweise ist dieser View aber leer.

Was mache ich falsch, und wie macht man es richtig?

Perl 6 - Perls Zukunft

bloonix

2007-10-25 11:36

User since
2005-12-17
1615 Artikel
HausmeisterIn

Du könntest dir zum Beispiel eine Tabelle days anlegen
und jedesmal, wenn eine Zeile mit einem neuen Tag in die
Tabelle irclog eingefügt wird, legst du einen neuen Tag
in die Tabelle days ab. Dabei musst du nur beachten, wenn
du Einträge älter als X aus der Tabelle irclogs löscht,
auch die betroffenen Tage aus Tabelle days zu löschen.

Auf diese Weise pflege ich auch einige Tabellen. Ich habe
zum Beispiel einige Tabellen mit mehreren Millionen einträgen,
die mit einem Timestamp versehen sind. Wenn ich jedesmal
ein Distinct auf die Tabelle ausführen müsste, wäre das horror.

So kann ich die Tabelle days selektieren und habe alle Tage,
für die es irclog-Einträge gibt.

What is a good module? That's hard to say.
What is good code? That's also hard to say.
One man's Thing of Beauty is another's man's Evil Hack.

2007-10-25 11:51

User since
2003-08-04
12208 Artikel
Admin1

evtl. im irc geklärt: einen index auf channel anlegen (und nicht wie bisher einen
kombinierten index)

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

Struppi

2007-10-25 14:48

User since
2006-02-17
628 Artikel
BenutzerIn

5-10 sekunden ist für die Anzahl der Einträge auf jeden Fall zu lang. was sagt dir den EXPLAIN?
http://dev.mysql.com/doc/refman/5.1/de/explain.htm...

renee

2007-10-25 15:02

User since
2003-08-04
14371 Artikel
ModeratorIn

siehe http://perlpunks.de/paste/show/4720459a.b79.3c4

hat er vorhin im IRC gepostet...

OTRS-Erweiterungen (http://feature-addons.de/)
Frankfurt Perlmongers (http://frankfurt.pm/)
--

Unterlagen OTRS-Workshop 2012: http://otrs.perl-services.de/workshop.html
Perl-Entwicklung: http://perl-services.de/

Struppi

2007-10-25 16:00

User since
2006-02-17
628 Artikel
BenutzerIn

sieht eigentlich ganz gut aus.

Die Domain gefällt mir =:-)

2007-10-25 16:18

User since
2003-08-04
12208 Artikel
Admin1

Struppi+2007-10-25 14:00:27--
sieht eigentlich ganz gut aus.

sieht gut aus? wenn die db knapp 1 mio zeilen durchsuchen muss?

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

Struppi

2007-10-25 16:48

User since
2006-02-17
628 Artikel
BenutzerIn

Naja, vom Prinzip USING INDEX ist ja immer ein gutes Zeichen, aber jetzt kommen meine Schwächen in mysql zum tragen ;-) ich sach am besten nichts mehr.

bloonix

2007-10-26 11:03

User since
2005-12-17
1615 Artikel
HausmeisterIn

pq+2007-10-25 14:18:46--
sieht gut aus? wenn die db knapp 1 mio zeilen durchsuchen muss?

wenn die db 1 mio zeilen durchsucht hätte, dann hätte sie nicht
den index benutzt sondern einen full table scan durchgeführt ;-)

What is a good module? That's hard to say.
What is good code? That's also hard to say.
One man's Thing of Beauty is another's man's Evil Hack.

2007-10-26 13:18

User since
2003-08-04
12208 Artikel
Admin1

bloonix+2007-10-26 09:03:54--
pq+2007-10-25 14:18:46--
sieht gut aus? wenn die db knapp 1 mio zeilen durchsuchen muss?

wenn die db 1 mio zeilen durchsucht hätte, dann hätte sie nicht
den index benutzt sondern einen full table scan durchgeführt ;-)

was willst du damit sagen? der output von EXPLAIN sagt eindeutig, dass der index
benutzt wurde, und trotzdem knapp 1mio zeilen durchsucht werden mussten, eben weil
es kein index auf channel allein war, sondern ein kombinierter index. dass mysql dann
gleich einen table scan hätte machen können, ist eine andere frage.

hier nochmal der output:

Code: (dl )

mysql> EXPLAIN SELECT DISTINCT(channel) FROM irclog;
+----+-------------+--------+-------+---------------+------------------------+---------+------+--------+------------------------------+
| id | select_type | table  | type  | possible_keys | key                    | key_len | ref  | rows   | Extra                        |
+----+-------------+--------+-------+---------------+------------------------+---------+------+--------+------------------------------+
|  1 | SIMPLE      | irclog | index | NULL          | irclog_day_channel_idx | 124     | NULL | 920939 | Using index; Using temporary | 
+----+-------------+--------+-------+---------------+------------------------+---------+------+--------+------------------------------+
1 row in set (0.00 sec)

rows: 920939 heisst nun mal, dass 920939 zeilen durchsucht werden.

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

View all threads created 2007-10-24 23:45.