Bitte helfen bei der Query-Optimierung und Index-Erstellung

[elcomportal]

Hallo,
ich habe ein paar Tabellen. Nehmen wir uns mal die gößte Tabelle vor.
Hier die Struktur:

CREATE TABLE `artikel_Mode` (
  `product_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `anbieter` varchar(64) NOT NULL,
  `ueberschrift` varchar(260) NOT NULL,
  `beschreibung` varchar(4512) NOT NULL,
  `bildlink` varchar(255) NOT NULL,
  `shoplink` varchar(512) NOT NULL,
  `preis` float NOT NULL,
  `marke` varchar(255) NOT NULL,
  `kat` varchar(12) NOT NULL,
  `lieferkosten` double NOT NULL,
  `lieferzeit` varchar(56) NOT NULL,
  `extra1` varchar(2000) NOT NULL,
  `extra2` varchar(2000) NOT NULL,
  `zupid` varchar(128) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14359062 ;

Ich hatte schon auf verschiedenste Arten Indexe erstellt, auch Index über mehrere Spalten, aber es hat nichts gebracht.

Hier erstmal die Query:

SELECT SQL_CALC_FOUND_ROWS ueberschrift, SUBSTRING(beschreibung,1,250),
bildlink,shoplink,preis,marke,anbieter,lieferkosten,lieferzeit,extra1,extra2,zupid,
kat FROM artikel_Mode WHERE kat IN ('1','3','100','132')
 
AND marke='der Hersteller'
 
AND MATCH (ueberschrift,beschreibung) AGAINST ('suchtext' IN BOOLEAN MODE)
 
ORDER BY ueberschrift ASC LIMIT 1,24
ORDER BY preis ASC  LIMIT 1,24
ORDER BY preis DESC LIMIT 1,24

Wobei die Eingrenzung nach Marke nur optional ist, genau wie die Volltext-Suche ...and Match...
Die 3 ORDER BY sind die 3 Varianten, die zur Zeit ausgewählt werden können.

Nun ist es so, daß die Abfrage mehr als 10 Sekunden dauert und das ist nicht befriedigend. Wie bereits erwähnt, habe ich schon verschiedene Arten der indexe probiert, aber es wird nicht schneller, oftmals benutzt MySQL den Index gar nicht (EXPLAIN ...).
Wie sollte ich den Index am Besten anlegen, so daß er benutzt wird und die Abfragen schneller werden. Die Where-Klausel kat IN('1'... kann auch schon mal 30 oder 40 Werte beinhalten, diese Werte kommen aus einem vom PHP-Script erstellten Array.
Was ich noch erwähnen sollte, die Daten werden per LOAD DATA INFILE in die Tabelle geschrieben, vorher werden alle Produkte des Anbieters gelöscht und dann werden die Daten in die Tabelle geschrieben. Das passiert jede Nacht.

Ich hoffe ihr könnt mir helfen.
Vielen Dank schonmal im Vorraus.

VG
Torsten

 

[akretschmer]

Nun ist es so, daß die Abfrage mehr als 10 Sekunden dauert und das ist nicht befriedigend. Wie bereits erwähnt, habe ich schon verschiedene Arten der indexe probiert, aber es wird nicht schneller, oftmals benutzt MySQL den Index gar nicht (EXPLAIN ...).
Wie sollte ich den Index am Besten anlegen, so daß er benutzt wird und die Abfragen schneller werden. Die Where-Klausel kat IN('1'... kann auch schon mal 30 oder 40 Werte beinhalten, diese Werte kommen aus einem vom PHP-Script erstellten Array.

MySQL funktioniert deutlich besser, wenn man es vor Nutzung gegen eine richtige Datenbank wie PostgreSQL austauscht:

test=# create table elcom (kat text);
CREATE TABLE
Time: 319,523 ms
test=*# insert into elcom select s::text from generate_series(1,1000000)s;
INSERT 0 1000000
Time: 3313,860 ms
test=*# create index idx_elcom on elcom (kat);
CREATE INDEX
Time: 11738,072 ms
test=*# explain select * from elcom where kat in ('1','3','100','132');
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on elcom  (cost=380.11..5105.11 rows=20000 width=32)
   Recheck Cond: (kat = ANY ('{1,3,100,132}'::text[]))
   ->  Bitmap Index Scan on idx_elcom  (cost=0.00..375.11 rows=20000 width=0)
         Index Cond: (kat = ANY ('{1,3,100,132}'::text[]))
(4 rows)

Warum speicherst Du kat als Text, wenn es doch offenbar immer INT sind? Warum verwendest Du MyISAM? Warum zeigst Du kein Explain? Wie sind die Werte in Kat verteilt, für wieviel Prozent der Tabelle gilt Dein Where?

 

[gurbelunder]

Muss man nicht manchmal bei MySQL genau wie bei Oracle die "Statistiken" auffrischen, damit MySQL schnallt, dass neue Idizes vorhanden sind, die er auch benutzen darf?

Wie hast du die Indizes angelegt?