[björn]

Hallo,

habe mittels dem Benchmarker von Pear Marken in mein Script gesetzt, damit ich sehe, welcher Code wie lange dauert.

Nun habe ich gesehen, dass ein bestimmter Query 80% der Ausführungszeit in Anspruch nimmt.

Habe den Query kurzerhand genommen und mit einem "EXPLAIN" davor in PHPMyAdmin ausgeführt.

Eigentlich komme ich mit dieser Variante recht schnell klar und kann auch gut optimieren.

Dieses Mal frage ich mich jedoch, was ich optimieren soll bzw welche Indizes ich zusätzlich setzten muss. Eigentlich sind nämlich alle Spalten, die in Joins vorkommen, schon Indizes.

naja, hier ein Screenshot des Query inkl. Auswertung in PhpMyAdmin. Ich hoffe, Ihr könnt mir helfen!!

http://img1.myimg.de/querye6a.jpg

Hier noch die Tabellestruktur und der Query in Textform:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Erstellungszeit: 11. Mai 2006 um 15:12
-- Server Version: 5.0.15
-- PHP-Version: 5.0.5
-- 
-- Datenbank: `funpage`
-- 

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `link_cats`
-- 

CREATE TABLE `link_cats` (
  `id` tinyint(2) unsigned NOT NULL,
  `name` varchar(100) NOT NULL default '',
  `cflag` tinyint(2) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `flag` (`cflag`)
) TYPE=MyISAM AUTO_INCREMENT=13 ;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `link_entries`
-- 

CREATE TABLE `link_entries` (
  `id` smallint(11) unsigned NOT NULL,
  `flag` tinyint(2) NOT NULL default '-1',
  `eingetragen` int(10) unsigned NOT NULL default '0',
  `freigeschaltet` int(10) unsigned NOT NULL default '0',
  `cat_id` tinyint(2) unsigned NOT NULL default '0',
  `beschreibung` varchar(100) NOT NULL default '',
  `link` varchar(255) NOT NULL default '',
  `extern` tinyint(2) NOT NULL default '1',
  `previewPic` varchar(255) NOT NULL default '',
  `hits` int(11) NOT NULL default '0',
  `voteAnzahl` smallint(10) unsigned NOT NULL default '0',
  `VoteSumme` mediumint(10) unsigned NOT NULL default '0',
  `poster` varchar(100) NOT NULL default '',
  `poster_hp` varchar(100) NOT NULL default '',
  `ip` varchar(15) NOT NULL default '',
  `mail` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `flag` (`flag`),
  KEY `link` (`link`),
  KEY `freigeschaltet` (`freigeschaltet`)
) TYPE=MyISAM AUTO_INCREMENT=5276 ;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `own_content`
-- 

CREATE TABLE `own_content` (
  `id` smallint(5) unsigned NOT NULL,
  `html` text NOT NULL,
  `description` varchar(255) NOT NULL default '',
  `time` int(10) unsigned NOT NULL default '0',
  `ip` varchar(15) NOT NULL default '',
  `used` tinyint(3) NOT NULL default '0',
  `last_used` int(10) unsigned NOT NULL default '0',
  `used_by_fl_id` smallint(5) unsigned NOT NULL default '0',
  `type` tinyint(2) NOT NULL default '1',
  `disNaviReferer` tinyint(2) NOT NULL default '1',
  `disNaviToplist` tinyint(2) NOT NULL default '1',
  `disNaviPartner` tinyint(2) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=132 ;

hier der Query:

SELECT     
        /*** AUS DER TABELLE OWN_CONTENT ***/ 
        o.id                 AS oid, 
        o.time                 AS otime, 
        o.disNaviReferer     AS showR, 
        o.disNaviToplist     AS showT, 
        o.disNaviPartner     AS showP, 
        o.description         AS odesc, 
        o.type, 
        o.used, 
        
        /*** AUS DER TABELLE LINK_CATS ***/ 
        c.id                 AS cid, 
        c.name                AS catname, 
        
        /*** AUS DER TABELLE LINK_ENTRIES ***/ 
        e.id                 AS eid, 
        e.beschreibung         AS linkdesc, 
        e.flag, 
        e.freigeschaltet     AS etime, 
        e.previewPic         AS prev,
        e.hits 

FROM own_content o 

LEFT JOIN         link_entries e 
             ON e.link = o.id 
LEFT JOIN         link_cats c 
             ON c.id = e.cat_id 
        
ORDER BY 
        oid desc 
LIMIT     0, 30

Danke!!
mfg
Björn

im zweifelsfall - wenn EXPLAIN SELECT nichts anzeigt, ist "ORDER BY" schuld.
... und es ist nach ein wenig nachdenken auch klar warum ORDER BY alles verlangsamt: mysql muss zuerst das volle, teure join machen, bevor es sortieren kann.

besser fährst du mit zwei queries:
SELECT o.id FROM ... ORDER BY ... LIMIT 0,30 ... erstmal die ids, die du brauchst, holen.
... und dann das 'größere' join mit SELECT ... LEFT JOIN ... LEFT JOIN ... ... WHERE o.id IN ( die ids aus dem vorherigen query) . beim zweiten query dann auf ORDER BY verzichten.

evtl. vor dem umbau der php-skripte erstmal mit EXPLAIN gucken, ob der full join mit ein paar sinnvollen o.ids im IN-statement wirklich auf's 'using temporary' verzichtet.
grüße
axo

[björn]

Ok, vielen Dank für deine Antwort. Ich habe geschaut, folgendes Ergebnis kam dabei heraus:

http://img1.myimg.de/190c.gif

Also erst alle o.id's auslesen: SELECT o.id FROM own_content ORDER BY o.id DESC und diese dann einfach in das IN() des 2. Queries hauen?

Die Tabelle link_entries ist ja immernoch auf type = All, also der schlchteste Typ. Kann man da noch irgendwas machen?


Was mich wundert: Ich habe noch nie mit Subqueries gearbeitet und habe das hier einfach mal ausprobiert:

http://img1.myimg.de/2d00.gif

So müsste ich nicht mehr den Umweg über PHP gehen und hätte alles schön einfach, aber der Query ist glaube ich wieder inperformanter. Denn hier geht type von der own_content Tabelle ebenfalls von range auf ALL.

[björn]

Nun hat sich doch noch ein Problem aufgetan:

Was, wenn ich aber mal nach einer Spalte sortieren will, wie z.b. link_entries.freigeschaltet oder link_cats.cat_id, und zusätzlich einschränkungen mit Where machen will?

Teilweise sieht mein Query nämlich auch so aus:

SELECT /*** AUS DER TABELLE OWN_CONTENT ***/ o.id AS oid, o.time AS otime, 
        o.disNaviReferer AS showR, o.disNaviToplist AS showT, o.disNaviPartner AS showP, o.description AS odesc, o.type, o.used,
        /*** AUS DER TABELLE LINK_CATS ***/ c.id AS cid, c.name AS catname, 
        /*** AUS DER TABELLE LINK_ENTRIES ***/ e.id AS eid, e.beschreibung AS linkdesc, e.flag, e.freigeschaltet AS etime, 
        e.previewPic as prev, e.hits 
FROM own_content o 
LEFT JOIN link_entries e ON e.link = o.id 
LEFT JOIN link_cats c ON c.id = e.cat_id 
WHERE type = 2 AND cid = 3 AND flag = -1
ORDER BY etime ASC
LIMIT 30, 30

Dann müsste ich ja wieder solch einen großen Join mit der Sortierung danach absetzten, um nur die o.id's zu bekommen.

Irgendwie ist das Mist ...

[björn]

sry, dass ich nochmal nerve, aber man muss das doch irgendwie optimieren können!? hat denn keiner eine Idee?