Optimierung großer Tabellen

[martinm79]

Hallo,

ich komme ständig in engpässe, weil die Tabellen wachsen.

Das ist der Aufbau der Nachrichtentabelle

CREATE TABLE IF NOT EXISTS `prefix_nachrichten` (
  `nachrichten_id` int(10) unsigned NOT NULL auto_increment,
  `ordner_id` int(10) unsigned NOT NULL default '0',
  `user_id_ein` int(10) unsigned NOT NULL default '0',
  `user_id_aus` int(10) unsigned NOT NULL default '0',
  `antwort_id` int(10) unsigned NOT NULL default '0',
  `del_ein` tinyint(1) unsigned NOT NULL default '0',
  `del_aus` tinyint(1) unsigned NOT NULL default '0',
  `datum_del_ein` datetime default NULL,
  `datum_del_aus` datetime default NULL,
  `datum_gesendet` datetime default NULL,
  `datum_gelesen` datetime default NULL,
  `datum_beantwortet` datetime default NULL,
  `vorschau` varchar(20) NOT NULL default '',
  `nachricht` text,
  PRIMARY KEY  (`nachrichten_id`),
  KEY `prefix__nachrichten_FKIndex1` (`user_id_ein`),
  KEY `prefix__nachrichten_FKIndex2` (`user_id_aus`),
  KEY `prefix__nachrichten_FKIndex3` (`ordner_id`),
  KEY `user_id_ein` (`ordner_id`,`user_id_ein`,`del_ein`,`nachrichten_id`),
  KEY `msgAus` (`user_id_aus`,`del_aus`,`nachrichten_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Ich möchte jetzt einfach nur 21 Datensätze abfragen.

SELECT 
	nachrichten_id, 
	user_id_aus  user_id, 
	DATE_FORMAT(datum_gesendet,'%d.%m.%y<br>%H:%i') datum_gesendet,
	datum_gelesen,
	datum_beantwortet,						
	vorschau
FROM prefix_nachrichten 
WHERE ordner_id = 0
AND user_id_ein = '%d'
AND del_ein = 0
ORDER BY nachrichten_id DESC
LIMIT 21;

Und die Abfrage braucht ca. 0.6 Sekunden.


Was kann man noch verbessern, damit die Abfrage schneller wird?


Wie machen das die großen Firmen, die Millionen Datensätze in den Tabellen haben? Setzten die nur gute spezielle Server ein oder gibt es da nich eine andere Vorgangsweise?

[asp2php]

EXPLAIN auswerten und entsprechend optimieren. Was soll das hier: ... AND user_id_ein = '%d'

[martinm79]

Was soll das hier: ... AND user_id_ein = '%d'

Ich glaube das war schon mein erster Fehler.

Eigendlich stand da die jeweilige User ID

AND user_id_ein ='1'

In diesem Fall würde MySql den Index nicht verwenden weil es ein anderer Typ ist?
Habe es jedenfalls auf

AND user_id_ein =1

geändert.

Welche Reihenfolge muß den der Index haben?

So wie er in der Where bedingung steht oder so wie es im Ergebnis von Explain bei possible_keys steht?

[tontechniker]

In diesem Fall würde MySql den Index nicht verwenden weil es ein anderer Typ ist?

In diesem Fall würde MySQL einfach nichts zurückgeben. Das Prozentzeichen möchtest du nur für LIKE als Wildcard verwenden.

Welche Reihenfolge muß den der Index haben?

Reihenfolge? Die Reihenfolge der Indizes spielt afaik überhaupt keine Rolle. Viel wichtiger ist, was dir EXPLAIN über die eingesetzten Indizes erzählt.

[asp2php]

Das soll das komplette Ergebnis von EXPLAIN sein?

[TBT]

das explain stimmt aber nicht mit der oben angegebenen Tabelle und der Abfrage überein.
Das "using where" dürfte gar nicht auftauchen!!