Query uses long time

MySQL & MariaDB Other MySQL® Questions
1

Hey NG

So, I am new here in the forum, so the first, I wan’t to say is: Hallo to everyone.

So, I use MySQL v4.1.16 server.
I have two tables

– Table: invnrbeschreibung

– DROP TABLE invnrbeschreibung;

CREATE TABLE invnrbeschreibung (
ID bigint AUTO_INCREMENT NOT NULL,
InvNr char(2) NOT NULL DEFAULT ‘0’,
Beschreibung varchar(100) NOT NULL,
/* Keys */
PRIMARY KEY (ID)
) ENGINE = MyISAM;

CREATE FULLTEXT INDEX fulltext
ON invnrbeschreibung
(InvNr, Beschreibung);

CREATE INDEX index
ON invnrbeschreibung
(Beschreibung);

– Table: artikelstamm

– DROP TABLE artikelstamm;

CREATE TABLE artikelstamm (
ID bigint AUTO_INCREMENT NOT NULL,
InvNr char(2) NOT NULL,
Lieferant int(2) UNSIGNED ZEROFILL NOT NULL DEFAULT ‘00’,
ArtikelNr varchar(6) NOT NULL,
Groesse varchar(4) NOT NULL,
Farbe varchar(5) NOT NULL,
ArtBez varchar(30) NOT NULL,
Mwst int(10) NOT NULL DEFAULT ‘20’,
VPreis int(10) NOT NULL DEFAULT ‘0’,
Lagermenge int NOT NULL DEFAULT ‘0’,
MengeVK int NOT NULL DEFAULT ‘0’,
WertVK int(10) NOT NULL DEFAULT ‘0’,
AnzVK int NOT NULL DEFAULT ‘0’,
DivArt char NOT NULL DEFAULT ‘0’,
Warnung int NOT NULL DEFAULT ‘0’,
iCode double(6,0) UNSIGNED ZEROFILL NOT NULL DEFAULT ‘000000’,
oCode double(20,0) NOT NULL DEFAULT ‘0’,
EPreis int(10) NOT NULL DEFAULT ‘0’,
Lagwert int(10) NOT NULL DEFAULT ‘0’,
InvMenge int NOT NULL DEFAULT ‘0’,
InvWert int(10) NOT NULL DEFAULT ‘0’,
DatlZug date NOT NULL DEFAULT ‘1900-12-31’,
DatlVK date NOT NULL DEFAULT ‘1900-12-31’,
DatlInv date NOT NULL DEFAULT ‘1900-12-31’,
DatSeit date NOT NULL DEFAULT ‘1900-12-31’,
DatlAenderung date NOT NULL DEFAULT ‘1900-12-31’,
/* Keys */
PRIMARY KEY (ID)
) ENGINE = MyISAM;

CREATE INDEX Einmalig
ON artikelstamm
(iCode);

CREATE INDEX Indiziert
ON artikelstamm
(Lieferant);

CREATE FULLTEXT INDEX indiziertfulltext
ON artikelstamm
(InvNr);

So, I have the following SQL Statement to get the Names from the Invnrbeschreibung fitting to the artikelstamm InvNr

SELECT DISTINCT artikelstamm.InvNr as InvNr, InvNrBeschreibung.Beschreibung as Beschreibung FROM voptneu.artikelstamm RIGHT OUTER JOIN voptneu.InvNrBeschreibung ON voptneu.artikelstamm.InvNr = voptneu.InvNrBeschreibung.InvNr ORDER BY InvNr

So, the table artikelstamm contains about 40000 entries & the table invnrbeschreibung contains about 30 entries

The query uses up to 12 seconds… - Is there any kind of performance tuning you could tell me?

THX

2

Please always post EXPLAIN for query you’re trying to get help with.

3

Hey
Thanks for reply

I’ve attached a JPEG, what Explain shows to me.

THX