GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Query uses long time

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query uses long time

    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.

    Comment


    • #3
      Hey
      Thanks for reply

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

      THX

      Comment

      Working...
      X