Performance Frage

[waldbauer.com]

Hallo MySQL Profis !

Ich habe 2 relativ simple Querys die bei mir teilweise extrem lange dauern. Obwohl ich schon an den my.ini Variablen herumgeschruabt habe komme ich einfach nicht auf ein performantes System.

$mails = $DB_site->query("
SELECT *
FROM hive_message AS message
WHERE folderid = $folderid
AND userid = $hiveuser[userid]
ORDER BY $sortby $sortorder
LIMIT ".($limitlower-1).", $perpage
");

Die Datenbank hat insgesamt ca. 20.000 Einträge, wenn ich mitlogge dann erhalte ich folgendes:

Dec 6 2006 12:21:51: User: xxx, SELECT*: 20.7263 sec. #: für 1318 Datensätze

Der betreffende MySQL Server hat 2GB Ram zur verfügung, läuft auf einem P4 3.2Ghz und ist sonst praktisch nicht beschäftigt. Die Datenbank MESSAGE hat etwa 1.5GB.

Die 2te Query ist:

$totalmails = $DB_site->get_field("
SELECT COUNT(*) AS count
FROM hive_message
WHERE ".iif(!isset($findemail), "folderid = $folderid AND ")."userid = $hiveuser[userid]
");


Meine aktuellen my.ini sind:

max_connections=500
interactive_timeout=100
wait_timeout=45
connect_timeout=10
table_cache=400
thread_cache_size=30
thread_concurrency=4
tmp_table_size=128M
max_heap_table_size=16MB
max_allowed_packet=64M
max_connect_errors=10
max_tmp_tables=64
key_buffer=100M
key_buffer_size=220M
record_buffer=6M
join_buffer_size=4M
read_buffer_size=4M
sort_buffer_size=6M
read_rnd_buffer_size=4M
bulk_insert_buffer_size=8M
myisam_block_size=4096
myisam_sort_buffer_size=64M
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G

query_cache_type=1
query_cache_limit=4M
query_cache_size= 128M
query_prealloc_size=163840
query_alloc_block_size=32768
default-storage-engine=MyISAM

In der Laufzeit von MySQL habe ich folgenden ROTE Werte:

Slow_queries 7
Handler_read_rnd 96 k
Handler_read_rnd_next 35 k
Created_tmp_disk_tables 10
Key_reads 8.157
Select_full_join 11
Opened_tables 671

Die Schlüssel in der Datenbank sind:

PRIMARY PRIMARY 24826 messageid
name INDEX 8275 name
userid INDEX 23 userid
folderid INDEX 101 folderid
email INDEX 8275 email
subject INDEX 12413 subject
status INDEX 29 status
attach INDEX 29 attach
emailid INDEX 12413 emailid
dateline INDEX 24826 dateline
priority INDEX 3 priority
popsize INDEX 8275 popsize
uniquestr INDEX 1 uniquestr
size INDEX 8275 size

[Onur]

hat die tabelle indexe? (folder+user)

warum "FROM hive_message AS message" ist das "AS message" unbedingt nötig?

[waldbauer.com]

folderid und userid - wie kommst auf folder und user ?

warum "FROM hive_message AS message" ist das "AS message" unbedingt nötig?

Ich weiss es nicht ?! Das ist Teil eines Webmailprogrammes, leider kann ich dir nicht sagen wozu das benötigt wird.

[Onur]

folderid und userid - wie kommst auf folder und user ?
ja die beiden, sieh mal mit phpmyadmin nach ob die beiden felder (welche eben in dem Querie sortiert werden) einen index haben, wenn nicht mal zum test einen anlegen und kontrollieren :rolleyes: (natürlich backup nicht vergessen)

Ich weiss es nicht ?! Das ist Teil eines Webmailprogrammes, leider kann ich dir nicht sagen wozu das benötigt wird.
na dann änder da mal nix, mir kommt es nur manchmal so vor als ob immer wenn ich mysql ein AS aufbrumme es langsamer geht als ohne, wenn dann der Querie an sich sowas nicht braucht lasse ichs halt weg, wenn aber womöglich was dynamisch dazukommt und die "gefahr" besteht das es dann zu nem fehler kommt würde ich da nix ändern ;)

[waldbauer.com]

Also die Indexe auf der DB hab ich auch im Beitrag stehen. Soviel ich gesehen habe, sind die für die Abfrage gesetzt.

[waldbauer.com]

Wenn ich mir die beiden Abfragen erklären lasse habe ich folgendes Ergebis:

mysql> explain
-> select * from hive_message as message
-> WHERE folderid = -1 AND userid = 2 order by dateline DESC LIMIT 0,100;
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | message | ref | userid,folderid | folderid | 4 | const | 1221 | Using where; Using filesort |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain
-> select * from hive_message as message
-> WHERE folderid = -3 AND userid = 2 order by dateline DESC LIMIT 0,100;
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | message | ref | userid,folderid | folderid | 4 | const | 7674 | Using where; Using filesort |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

Current database: hivemail
-> SELECT COUNT(*) AS count FROM hive_message WHERE folderid = -3 AND userid
= 2;
+----+-------------+--------------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------------------+
| 1 | SIMPLE | hive_message | index_merge | userid,folderid | folderid,userid | 4,4 | NULL | 4007 | Using intersect(folderid,userid); Using where; Using index |
+----+-------------+--------------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------------------+
1 row in set (0.20 sec)

[waldbauer.com]

Wenn ich mir aber die zuheörigen Logs ansehe brauchen die Abfragen viel länger:

Dec 6 2006 18:13:13: User: xxx, SELECT*: 23.7424 sec. #: 1325
Dec 6 2006 18:13:13: User: xxx, SELECT*: 3.4995 sec. #: 1325
Dec 7 2006 11:32:46: User: xxx, SELECT*: 5.4444 sec. #: 3
Dec 7 2006 11:32:46: User: xxx, SELECT*: 4.474 sec. #: 3
Dec 7 2006 11:32:46: User: xxx, SELECT*: 3.479 sec. #: 3
Dec 7 2006 13:31:56: User: xxx, SELECT*: 20.412 sec. #: 1351

[waldbauer.com]

Guten Morgen !

Frage schon wieder. MySql benutzt bei einfachen Abfragen keinen Index - woran kann liegen ?

Bsp:
EXPLAIN SELECT * FROM hive_message WHERE subject LIKE '%hotmail%';

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE hive_message ALL NULL NULL NULL NULL 19789 Using where

[mainlink]

Mußt Du wirklich SELECT * nutzen? Das ist sehr performancebeanspruchend; besser wäre es, die einzelnen Spalten einzeln aufzurufen, also:

SELECT foo,bar,blubb,bla FROM

[waldbauer.com]

Aber wenn ich danach alle Spalten brauche ?

[mainlink]

Dann geht das natürlich nicht...

Ich kenne Deinen speziellen Fall nicht, aber meistens ist SELECT * FROM eine eher faule, performancebelastende Abfrage, was nicht auf diesen speziellen Fall zutreffen mag.

Gruß, m.