MySQL Performance: MySQL => Query uses long time

Query uses long time [message #1568]

Wed, 01 August 2007 08:58

ZeroG
Messages: 2
Registered: August 2007
Location: Austra

Junior Member

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

Report message to a moderator