Re: why my db don't use index?

[Rick James]

Please provide the counts and times for
SELECT COUNT(*) FROM artikel_Mode;
SELECT COUNT(*) FROM artikel_Mode
WHERE MATCH (ueberschrift,beschreibung) AGAINST ('wolle' IN BOOLEAN MODE) ;
SELECT COUNT(*) FROM artikel_Mode
WHERE MATCH (ueberschrift,beschreibung,anbieter,marke) AGAINST ('wolle' IN BOOLEAN MODE) ;

> Another question. I have to update all productdata in every night. Is it better to write them in temporary tables and after all data imported to rename to the right table-names? I think, the index will new created every night and it will be better as a update of the tables, isn't it?

It depends.

Do you have sufficient time every night to perform the operation? Of so, then I say "if it ain't broke, don't fix it".

If the reload causes downtime, and this is undesirable, then two options (both of which you mentioned):

Plan A:
CREATE TABLE new LIKE artikel_Mode;
ALTER TABLE new DISABLE KEYS;
LOAD ... into artikel_Mode ...;
ALTER TABLE new ENABLE KEYS; -- This might work faster than doing the indexing during the LOAD.
RENAME TABLE artikel_Mode TO old, new TO artikel_Mode;
DROP TABLE old;
There will be zero downtime. The RENAME is 'instantaneous' and atomic.

Plan B:
Load the new data into a temp table.
INSERT INTO artikel_Mode SELECT ... FROM artikel_Mode RIGH JOIN temp ON ... WHERE ... IS NULL -- to get new rows.
DELETE artikel_Mode LEFT JOIN temp ON ... WHERE ... IS NULL -- to remove missing rows.
UPDATE artikel_Mode JOIN temp ON ... SET ... WHERE ... (any differences) -- update rows.

If the nightly load has only a few differences, there might be some advantage of Plan B.