mysql JOIN optimierung

tischi
Posts: 215
Joined: 2003-10-26 17:57

mysql JOIN optimierung

Post by tischi »

moin moin,
da mitlerweille abend zur "rush Hour" die mysqld last auf 60-80% CPU last hoch gerade bin ich stark am optimieren, wie am Server als auch am Script.

Ich habe folgendes und nicht für mich verständliches Prob.:

Code: Select all

SELECT * FROM login LEFT JOIN steckbrief USING(usr) WHERE login.expire_onlinelist >= 1189876882 AND login.expire <= 1189876882 ORDER by geschlecht DESC,age DESC;


ich habe in der Datenbank Folgende Index's gesetzt:
login.usr
steckbrief.usr
login.expire_onlinelist
login.expire

tritzdem haufen sich die logs ala:

Code: Select all

Query_time: 0  Lock_time: 0  Rows_sent: 26  Rows_examined: 136


versteh das nicht hab auch schon gegoogled ^^ :roll:

danke schon mal im vorraus.
achja, fals wichtig >MyISAM<
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql JOIN optimierung

Post by Joe User »

Wie lautet Deine Frage?
Glaskugel:

Code: Select all

SELECT l.usr, l.expire_onlinelist, l.expire, s.usr, s.geschlecht, s.age
FROM login l
LEFT JOIN steckbrief s
ON (l.usr = s.usr)
WHERE l.expire_onlinelist >= 1189876882
AND l.expire <= 1189876882
ORDER by s.geschlecht DESC, s.age DESC;
Top

tischi
Posts: 215
Joined: 2003-10-26 17:57

Re: mysql JOIN optimierung

Post by tischi »

*g* versteh nicht, warum er erst einen Table Scan macht:

Code: Select all

Rows_examined: 136
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql JOIN optimierung

Post by Joe User »

http://dev.mysql.com/doc/refman/5.0/en/ ... ation.html
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

* You use ORDER BY on different keys:

SELECT * FROM t1 ORDER BY key1, key2;
Top

tischi
Posts: 215
Joined: 2003-10-26 17:57

Re: mysql JOIN optimierung

Post by tischi »

mh, und nun? *g*

was anderes:

Code: Select all

# Query_time: 0  Lock_time: 0  Rows_sent: 10  Rows_examined: 3823
SELECT * FROM forum_entries WHERE id='4055' OR id_reply='4055' ORDER BY 'datum' ASC LIMIT 0 , 10;


ich habe indexe's auf id,id_reply und datum... trotzdem macht er einen table scan :( liegt das da auch am order by?
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql JOIN optimierung

Post by Joe User »

Dazu gibt das Manual leider nichts genaues her, http://dev.mysql.com/doc/refman/5.0/en/ ... ation.html
In some cases, MySQL handles a query differently when you are using LIMIT row_count and not using HAVING:

* If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: mysql JOIN optimierung

Post by Joe User »

Top

tischi
Posts: 215
Joined: 2003-10-26 17:57

Re: mysql JOIN optimierung

Post by tischi »

ich teste gerade mal was, sehr interessant ist folgendes:

Code: Select all

SQL-Befehl: EXPLAIN SELECT * FROM forum_entries WHERE id='4055' OR id_reply='4055'; 
possible_keys :PRIMARY,id_reply
key: NULL
rows: 4861


Code: Select all

SQL-Befehl: EXPLAIN SELECT * FROM forum_entries WHERE id='4055'; 
possible_keys :PRIMARY
key: PRIMARY
rows: 1


also liegt es nicht mal am order by bzw. limit... mh? versteh ich nicht.
Top

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: mysql JOIN optimierung

Post by isotopp »

Tischi wrote:ich teste gerade mal was, sehr interessant ist folgendes:

Code: Select all

SQL-Befehl: EXPLAIN SELECT * FROM forum_entries WHERE id='4055' OR id_reply='4055'; 
possible_keys :PRIMARY,id_reply
key: NULL
rows: 4861


Code: Select all

SQL-Befehl: EXPLAIN SELECT * FROM forum_entries WHERE id='4055'; 
possible_keys :PRIMARY
key: PRIMARY
rows: 1


also liegt es nicht mal am order by bzw. limit... mh? versteh ich nicht.


Paste mal

SELECT VERSION();
SHOW CREATE TABLE forum_entriesG
SHOW TABLE STATUS LIKE "forum_entires"G

und was passiert, wenn Du die '' um die Zahlen wegpopelst mit dem EXPLAIN?
Top

tischi
Posts: 215
Joined: 2003-10-26 17:57

Re: mysql JOIN optimierung

Post by tischi »

Version: 4.1.11

Tabellenstruktur:

Code: Select all

CREATE TABLE `forum_entries` (
  `id` int(11) NOT NULL auto_increment,
  `id_cat` int(11) NOT NULL default '0',
  `id_reply` int(11) NOT NULL default '0',
  `titel` varchar(50) NOT NULL default '',
  `text` text NOT NULL,
  `datum` varchar(20) NOT NULL default '',
  `usr` varchar(25) NOT NULL default '',
  `visits` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `id_cat` (`id_cat`),
  KEY `id_reply` (`id_reply`),
  KEY `datum` (`datum`),
  KEY `usr` (`usr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5441 ;

ohne die ' ' das gleiche ergebniss

//edit:
zum mysql auch noch was:
ø pro Sekunde 78,27
ø pro Minute 4,70 k
ø pro Stunde 281,76 k
Top

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: mysql JOIN optimierung

Post by isotopp »

[quote="Tischi"]

Code: Select all

  `id` int(11) NOT NULL auto_increment,
  `id_reply` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `id_reply` (`id_reply`),



Der Optimizer zieht beide Indices in Betracht, wählt sie aber nicht (für 4055). Bleibt das nach einem ANALYZE TABLE so?

Außerdem: Kannst Du die Query in ein UNION oder UNION ALL auseinanderziehen und verbessert sich der Plan dadurch?

EXPLAIN
SELECT * FROM forum_entries WHERE id=4055
UNION
SELECT * FROM forum_entries WHERE id_reply=4055;

Das ist das, was der 5.0 Optimizer aus dem OR machen würde.
Top

tischi
Posts: 215
Joined: 2003-10-26 17:57

Re: mysql JOIN optimierung

Post by tischi »

nach einem ANALYZE TABLE blieb es auch so!

Code: Select all

EXPLAIN 
SELECT * FROM forum_entries WHERE id=4055
UNION
SELECT * FROM forum_entries WHERE id_reply=4055;


;) perfekt, einmal 1 row und einmal 22 rows, damit lässt es sich leben. schaue mir das nachher nochmal genau an die docs für UNION... muss jetzt leider weg.
Top

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: mysql JOIN optimierung

Post by isotopp »

Tischi wrote:nach einem ANALYZE TABLE blieb es auch so!

Code: Select all

EXPLAIN 
SELECT * FROM forum_entries WHERE id=4055
UNION
SELECT * FROM forum_entries WHERE id_reply=4055;


;) perfekt, einmal 1 row und einmal 22 rows, damit lässt es sich leben. schaue mir das nachher nochmal genau an die docs für UNION... muss jetzt leider weg.


Würdest Du eine zeitgemäße MySQL Version verwenden, träte das Problem nicht mal auf.
Top

tischi
Posts: 215
Joined: 2003-10-26 17:57

Re: mysql JOIN optimierung

Post by tischi »

;) ja mal schaun muss mal von sarge auf etch updaten ^^ dann hab ich ja eine aktuelle
Top