MySQL JOIN zu langsam

J

jojojan

Mitglied
Hallo Leute,
ich hab mal wieder ein kleines Problem :(.
Seit einem Server-Umzug habe ich starke performance Probleme.
Mein Provider war so freundlich mir meine MySQL Slow Querie Log zur Verfügung zu stellen.(Es handelt sich um einen managed v-Server).
Allerdings bin ich wohl blind, weil ich sehe momentan keine Möglichkeit wie ich das optimieren soll. Ich sitze da nun schon seit Tagen dran und sehe wohl vor lauter Bäumen den Wald nicht mehr.
Vielleicht hat ja einer von euch einen geistesblitz. Ich bin über jede Hilfe/Tipp dankbar.
Das ist die Abfrage um die es geht (Query_time: 10.468401):
Code: 
SELECT l.id,l.pic,l.online,l.o_pic,l.nick,l.profile,l.l_details,m.u_gelesen FROM ladys AS l 
										LEFT JOIN message AS m ON m.lady_id = l.id AND  m.user_id = '9262' AND  m.u_gelesen = '0'
										WHERE l.online = '1' 
										GROUP BY l.nick ORDER BY m.u_gelesen DESC;

Hier ist der Tabellenaufbau:
Code: 
CREATE TABLE `ladys` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `nick` varchar(25) NOT NULL,  `profile` text NOT NULL,  `l_details` text NOT NULL,  `l_notes` text NOT NULL,  `pic` varchar(999) NOT NULL,  `o_pic` varchar(999) NOT NULL,  `album` text NOT NULL,  `datum` int(11) NOT NULL,  `mod_id` int(11) NOT NULL DEFAULT '0',  `online` int(1) NOT NULL DEFAULT '0',  `online_since` int(11) NOT NULL DEFAULT '0',  `advertising` int(1) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  KEY `mod_id` (`mod_id`),  KEY `online` (`online`),  KEY `advertising` (`advertising`),  
KEY `online_since` (`online_since`)) ENGINE=MyISAM AUTO_INCREMENT=430 DEFAULT CHARSET=utf8

Code: 
Name
ladys
Engine
MyISAM
Version
10
Row_format
Dynamic
Rows
419
Avg_row_length
2366
Data_lentgh
991768
Max_data_length
281474976710655
Index_length
33792
Data_free
128
Auto_increment
430
Collation
utf8_general_ci

Code: 
CREATE TABLE `message` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` int(11) NOT NULL,  `lady_id` int(11) NOT NULL,  `send_by` int(2) NOT NULL,  `msg` varchar(900) NOT NULL,  `datum` int(11) NOT NULL,  `u_gelesen` int(1) NOT NULL DEFAULT '0',  `l_gelesen` int(1) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  KEY `user_id` (`user_id`),  KEY `lady_id` (`lady_id`),  KEY `datum` (`datum`),  KEY `u_gelesen` (`u_gelesen`),  KEY `l_gelesen` (`l_gelesen`)) ENGINE=MyISAM AUTO_INCREMENT=278455 DEFAULT CHARSET=utf8

Code: 
Name
message
Engine
MyISAM
Version
10
Row_format
Dynamic
Rows
196967
Avg_row_length
74
Data_lentgh
14687640
Max_data_length
281474976710655
Index_length
12294144
Data_free
0
Auto_increment
278462
Collation
utf8_general_ci

Mysql Einstellungen:
Code: 
Variable_name 	Value
bulk_insert_buffer_size 	8388608
innodb_blocking_buffer_pool_restore 	OFF
innodb_buffer_pool_instances 	1
innodb_buffer_pool_populate 	OFF
innodb_buffer_pool_restore_at_startup 	0
innodb_buffer_pool_shm_checksum 	ON
innodb_buffer_pool_shm_key 	0
innodb_buffer_pool_size 	134217728
innodb_change_buffering 	all
innodb_log_buffer_size 	8388608
join_buffer_size 	131072
key_buffer_size 	536870912
myisam_sort_buffer_size 	50331648
net_buffer_length 	16384
preload_buffer_size 	32768
read_buffer_size 	524288
read_rnd_buffer_size 	262144
sort_buffer_size 	16777216
sql_buffer_result 	OFF

Und das sagt EXPLAIN:
Code: 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	l 	ref 	online 	online 	4 	const 	74 	Using where; Using temporary; Using filesort
1 	SIMPLE 	m 	ref 	user_id,lady_id,u_gelesen 	user_id 	4 	const 	96

Wie gesagt ich bin über jede Hilfe oder jeden noch so kleinen Tipp dankbar :)

Gruß
jojojan
 
Mal das SQL in eine lesbare Form formatieren (http://yaslaw.info/sqlformatter/)
SQL: 
SELECT 
    l.id, 
    l.pic, 
    l.online, 
    l.o_pic, 
    l.nick, 
    l.profile, 
    l.l_details, 
    m.u_gelesen 
FROM 
    ladys AS l 
    LEFT JOIN message AS m ON m.lady_id = l.id 
    AND m.user_id = '9262' 
    AND m.u_gelesen = '0' 
WHERE 
    l.online = '1' 
GROUP BY 
    l.nick 
ORDER BY 
    m.u_gelesen DESC;
SQL: 
CREATE TABLE `ladys` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `nick` VARCHAR(25) NOT NULL, 
    `profile` text NOT NULL, 
    `l_details` text NOT NULL, 
    `l_notes` text NOT NULL, 
    `pic` VARCHAR(999) NOT NULL, 
    `o_pic` VARCHAR(999) NOT NULL, 
    `album` text NOT NULL, 
    `datum` int(11) NOT NULL, 
    `mod_id` int(11) NOT NULL DEFAULT '0', 
    `online` int(1) NOT NULL DEFAULT '0', 
    `online_since` int(11) NOT NULL DEFAULT '0', 
    `advertising` int(1) NOT NULL DEFAULT '0', 
    PRIMARY KEY (`id`), 
    KEY `mod_id` (`mod_id`), 
    KEY `online` (`online`), 
    KEY `advertising` (`advertising`), 
    KEY `online_since` (`online_since`)
) ENGINE = MYISAM AUTO_INCREMENT = 430 DEFAULT CHARSET = utf8
SQL: 
CREATE TABLE `message` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL, 
    `lady_id` int(11) NOT NULL, 
    `send_by` int(2) NOT NULL, 
    `msg` VARCHAR(900) NOT NULL, 
    `datum` int(11) NOT NULL, 
    `u_gelesen` int(1) NOT NULL DEFAULT '0', 
    `l_gelesen` int(1) NOT NULL DEFAULT '0', 
    PRIMARY KEY (`id`), 
    KEY `user_id` (`user_id`), 
    KEY `lady_id` (`lady_id`), 
    KEY `datum` (`datum`), 
    KEY `u_gelesen` (`u_gelesen`), 
    KEY `l_gelesen` (`l_gelesen`)
) ENGINE = MYISAM AUTO_INCREMENT = 278455 DEFAULT CHARSET = utf8
item: Da fällt mir sofort auf, dass du Festwerte im JOIN vergleichst. Sehr sehr unschön
AND m.user_id = '9262'
AND m.u_gelesen = '0'
Dieser Teil gehört ganz klar in den WHERE-Teil einer Tabelle. In diesem Fall in eine Unterabfrage.

item: Zudem kannst du den GROUP BY sparen und ein DISTINCT setzen. Du verwendest keiner Gruppierungsfunktionen (SUM(), COUNT() etc.).

Das ergibt etwa sowas
SQL: 
SELECT DISTINCT
    l.id, 
    l.pic, 
    l.online, 
    l.o_pic, 
    l.nick, 
    l.profile, 
    l.l_details, 
    m.u_gelesen 
FROM 
    ladys AS l 
    LEFT JOIN (
        SELECT  u_gelesen, lady_id
        FROM    message 
        WHERE   user_id = 9262
            AND u_gelesen = 0
    )AS m 
        ON m.lady_id = l.id 
WHERE 
    l.online = 1


item: Den Befehl KEY bei den CREATE TABLES kenn ich eigentlich nur im Zusammenhang mit einer Partitionierung.

item: Indexe fehlen. Bei der Lady währe das ein Index auf die Kombination id+online, neim Message auf u_gelesen, user_id und lady_id


Performance-Verbesserungen sind keine eindeutige und einfache Sache. Es ist ein testen und heran tasten. Auswerten von Explainplans, SQL umschreiben - ggf. total umschreiben, weiter testen, Anzahl Datensätze der Tabelle vergleichen, ggf. Subqueries machen um die Datenmenge so früh wie möglich klein zu kriegen etc.
Also, so einfach *schnipp*und ein SQL ist schnell - das kannst du vergessen. Diese ganzen Punkte die ich aufgezählt habe gehen auch nicht einfach so mit im Forum hin und her schreiben. Lies dich in das Thema ein. Versuch es zu verstehen. Und dann setz dich hin und probier dich durch.
 
Zuletzt bearbeitet von einem Moderator:
Nur zwei kleine Dinge:

1. user_id, u_gelesen und online sind alles INT, also keine Hochkommas. Anstelle '1' einfach 1

2. Wenn du was über Indizes lernen willst, schau hier nach. Ist eine gute Quelle:
http://use-the-index-luke.com/de

@Yaslaw: sehr schön, hätte ich auch so gemacht. Nur was kleines (ich nehme mal an das war ein Typo): group by kann weg in deinem Code. Der order by hingegen kann hin, so wie im ersten Script.
 
Merci BaseBallBatBoy.
Ich habe meinen Lösungsvorschlag angepasst. GROUP BY entfernt (C&P-Fehler) und die Zahlen als Zahlenvergleich geschrieben. Der ORDER BY mach keinen Sinn, da auf das Feld `u_gelesen` eh ein Filter auf 0 gesetzt ist. Ergo ist bei allen entweder 0 oder NULL
 
Hallo ihr beiden,
vielen Dank für eure Hilfe!
Ich werde das mal ausprobieren und mich da mal durcharbeiten.

Nochmal besten Dank an euch!
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück