mysql JOIN optimierung

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

[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;

[tischi]

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

Code: Select all

Rows_examined: 136

[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;

[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?

[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.

[Joe User]

http://dev.mysql.com/doc/refman/5.0/en/index-hints.html dürfte aber nicht viel verbessern.

[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.

[isotopp]

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?

[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

[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.

[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.

[isotopp]

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.

[tischi]

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