Was? Schon wieder überlastet? Jetzt haben wir doch gerade vier neue Server gekauft. Wollen wir nun weiter Hardware kaufen? Nein: Das erste, was wir in solchen Fällen machen, ist die Analyse des Slow Query Log der MySQL.

Buy Robohelp Server 7 Cheap microsoft project software purchase Cheap Acrobat 9 Extended Oem microsoft office specials Purchase Oracle Database 11g Release 2 microsoft exchange server 2003 enterprise Purchase Contribute Cs5 microsoft publisher software free download Buy Visual Studio 2010 Professional Edition microsoft windows xp professional upgrade Cheap Focalpoint 10 Oem microsoft office word 2003 Cheap Media Composer 5 Oem microsoft office student edition price Purchase Mcafee Network Access Control Serverall microsoft office 2003 activation Best Price Sony Acid Pro 7 adobe 8.1.1 reader software Buy Acronis Disk Director 11 Cheap buy microsoft office 2007 cheap Cheap Ptc Pro Engineer 40 Oem buy microsoft office oem Buy Total Defrag 2010 thomson microsoft office 2003 templates Buy Creative Suite 4 microsoft office professional 2007 full version Purchase Dtsearch Uk Engine 766 microsoft office software cheap Purchase Adobe Acrobat X Pro microsoft office compatible software Buy Ahead Nero 10 Cheap buy microsoft office pro Purchase Windows Xp Corporateall microsoft office xp 2002 Cheap Acronis Disk Director 11 Oem buy microsoft office small business Cheap Microsoft Windows Vista Build 5728 Automated Installation Kit Oem discount for microsoft office 2007 Purchase Solidworks 3d Cad buy microsoft software Buy 3dmax 2011 Cheap buy software microsoft office 2008 mac Best Price Fireworks Cs4 microsoft office certification training Buy Tomtom Map Of Southern Africa microsoft office 2000 download Purchase After Effects Cs5 X64 buy microsoft windows vista home basic with sp2 (32bit) for cheap Best Price Creative Suite 4 microsoft oem hardware download software Cheap Maya 2012 Oem buy microsoft powerpoint Buy Autodesk Navisworks Review 2010 Cheap microsoft office professional 2003 free student download Purchase Media Composer 5 microsoft office training in london Purchase Tomtom Map Of Western Central Europe microsoft exchange server 2003 enterprise Purchase Qsetup Installation Suite Pro 10 microsoft publisher software free download Best Price Windows Xp Sp3 microsoft windows xp professional upgrade Buy Incopy Cs55 Cheap microsoft office word 2003 Buy Autodesk Motionbuilder 2012 microsoft office student edition price Cheap Cyberlink Youcam 3 Oem microsoft office 2003 activation Best Price Illustrator Cs5 For Mac adobe 8.1.1 reader software Cheap Adobe Dreamweaver Cs5 V11 Oem buy microsoft office 2007 cheap Best Price Adobe Flash Cs5 Professional 11 For Mac buy microsoft office oem Cheap Tomtom Maps Of Scandinavia Oem thomson microsoft office 2003 templates Cheap Photoshop Cs5 Oem microsoft office professional 2007 full version Cheap Indesign Cs4 Oem microsoft office software cheap Purchase Flash Cs5 Professionalall microsoft office compatible software Cheap Adobe Creative Suite 55 Production Premium Oem buy microsoft office pro Best Price Premiere Elements 9 microsoft office xp 2002 Purchase Roxio Popcorn 4 For Mac buy microsoft office small business Buy Microsoft Office 2010 Web Application discount for microsoft office 2007 Buy Mcafee Quarantine Manager 70 Cheap buy microsoft software Buy Autocad Raster Design 2010 Cheap buy software microsoft office 2008 mac Buy Rosetta French 3 Levels 32 Cheap microsoft office certification training Cheap Visual Communicator Oem microsoft office 2000 download Cheap Twistedbrush 17 Oem buy microsoft windows vista home basic with sp2 (32bit) for cheap Buy Anydvd 66 Cheap microsoft oem hardware download software Cheap Tomtom Map Of United Kingdom Republic Of Ireland Oem buy microsoft powerpoint Cheap Premiere Pro Cs55 Oem microsoft office professional 2003 free student download Purchase Incopy Cs55 Cheap microsoft office training in london Purchase Acrobat Pro X Suite microsoft exchange server 2003 enterprise Purchase Microsoft Windows Xp 2010 Live Usb Edition microsoft publisher software free download Buy Mindmap For Projects 62 Cheap microsoft windows xp professional upgrade Purchase Tomtom Maps Of Germany Austria Switzerland microsoft office word 2003 Buy Office 2007 Professional microsoft office student edition price Purchase Adobe Creative Suite 4 Master Collection For Mac microsoft office 2003 activation Buy Adobe Indesign Cs4 adobe 8.1.1 reader software Buy Adobe Dreamweaver Cs4 Portable buy microsoft office 2007 cheap Buy Powercinema 6 buy microsoft office oem Purchase Installshield 2010 thomson microsoft office 2003 templates Cheap Audition 3 Oem microsoft office professional 2007 full version Buy Tomtom Maps Of Australia Cheap microsoft office software cheap Purchase Photoshop Elements 9all microsoft office compatible software Cheap Pixologic Zbrush 40 Oem buy microsoft office pro Purchase Corel Paintshop Photo Pro X3 132 microsoft office xp 2002 Purchase Turbotax 2010 Premier buy microsoft office small business Purchase Adobe Dreamweaver Cs4 Portablecategory discount for microsoft office 2007 Purchase Adobe Creative Suite 55 Design Premium Cheap buy microsoft software Buy Biztalk Server 2010 Cheap buy software microsoft office 2008 mac Best Price Paragon Drive Backup Server 10 microsoft office certification training Purchase Adobe Incopy Cs55 Cheap microsoft office 2000 download Purchase Avid Media buy microsoft windows vista home basic with sp2 (32bit) for cheap Cheap Adobe Creative Suite 55 Master Collection For Mac Oem microsoft oem hardware download software Buy Microsoft Windows Vista Build 5744 X64 Cheap buy microsoft powerpoint Cheap Autodesk Autocad 2011 X32 Oem microsoft office professional 2003 free student download Cheap Rosetta Spain 3 Levels 32 Oem microsoft office training in london Cheap Ableton Ag Suite 82 Oem microsoft exchange server 2003 enterprise Buy Autodesk Imagemodeler Sp1 2010 Cheap microsoft publisher software free download Buy Quicken Cash Manager 2010 R2 Canadian Edition Cheap microsoft windows xp professional upgrade Best Price Symantec Endpoint Protection 11 microsoft office word 2003 Buy Microsoft Office Project 2007 Pro 120 Cheap microsoft office student edition price Buy Dtsearch Uk Engine 766 Cheap microsoft office 2003 activation Cheap Creative Suite 5 Web Premium For Mac Oem adobe 8.1.1 reader software Buy Windows 7 Ultimate Cheap buy microsoft office 2007 cheap Purchase Powercinema 6 buy microsoft office oem Purchase Creative Suite 5 Master Collection Mac thomson microsoft office 2003 templates Buy Indesign Cs4 microsoft office professional 2007 full version Purchase After Effects Cs55 Cheapall microsoft office software cheap Buy Microsoft Windows 7 Starter Edition microsoft office compatible software Purchase Mathematica 7 buy microsoft office pro Buy Autocad Electrical 2012 microsoft office xp 2002 Buy Adobe Robohelp Server 7 buy microsoft office small business Buy Autodesk Autocad 2011 X64 Cheap discount for microsoft office 2007 Cheap Photoshop Cs5 buy microsoft software Best Price Acronis Disk Director 11 buy software microsoft office 2008 mac Purchase Project 2007 Pro microsoft office certification training Cheap Adobe Creative Suite 5 Web Premium For Mac Oem microsoft office 2000 download Cheap Mathematica 7 Oem buy microsoft windows vista home basic with sp2 (32bit) for cheap Cheap Total Defrag 2010 Oem microsoft oem hardware download software Cheap Mymp3pro 5 Oem buy microsoft powerpoint Buy Tomtom Maps Of Italy microsoft office professional 2003 free student download Purchase Adobe Audition 30 microsoft office training in london Purchase Motion Builder 2010 microsoft exchange server 2003 enterprise Cheap Framemaker 8 Oem microsoft publisher software free download Best Price Adobe Acrobat Professional X Suite microsoft windows xp professional upgrade Purchase Creative Suite 4 microsoft office word 2003 Buy Creative Suite 55 Master Collection microsoft office student edition price Purchase Adobe Dreamweaver Cs4 Portable microsoft office 2003 activation Best Price Cs4 Production Premium adobe 8.1.1 reader software Purchase Doublecad Xt Pro 21 buy microsoft office 2007 cheap Best Price Sql Server 2010 buy microsoft office oem Purchase Adobe Photoshop Cs4 Portable thomson microsoft office 2003 templates Cheap Creative Suite 5 Master Collection Oem microsoft office professional 2007 full version Best Price Originpro 75 microsoft office software cheap Cheap Apple Service Diagnostic For Mac Oem microsoft office compatible software Purchase Photoshop Cs5 Extended Mac buy microsoft office pro Purchase After Effects Cs4 microsoft office xp 2002 Cheap Mcafee Device Control 90 Oem buy microsoft office small business Purchase Microsoft Office 2011 Home Business For Mac discount for microsoft office 2007 Buy Cs 55 Master Collection Cheap buy microsoft software Purchase Electric Rain Swift 3d 6all buy software microsoft office 2008 mac Best Price Hard Disk Manager 2010 Server Portable microsoft office certification training Buy Roxio Creator 2011 Pro microsoft office 2000 download Best Price Illustrator Cs5 buy microsoft windows vista home basic with sp2 (32bit) for cheap Best Price Cs4 Design Premium microsoft oem hardware download software Purchase Rosetta Hebrew 2 Levels V3 buy microsoft powerpoint Best Price Acronis Backup And Recovery 10 microsoft office professional 2003 free student download Purchase Tomtom Maps Of France microsoft office training in london Buy Steinberg Cubase 5 Cheap microsoft exchange server 2003 enterprise Purchase Acdsee Pro 3 microsoft publisher software free download Cheap Adobe Incopy Cs5 Oem microsoft windows xp professional upgrade Cheap Soundbooth Cs5 Oem microsoft office word 2003 Best Price After Effects Cs55 microsoft office student edition price Best Price Ahead Nero 10 microsoft office 2003 activation Buy Photoshop Cs4 adobe 8.1.1 reader software Cheap Flash Cs5 Professional Oem buy microsoft office 2007 cheap Purchase Flash Catalyst Cs5 buy microsoft office oem Cheap Dreamweaver Cs55 Oem thomson microsoft office 2003 templates Purchase Adobe Photoshop Cs4 Extended microsoft office professional 2007 full version Buy Adobe After Effects Cs4 Cheap microsoft office software cheap Best Price Cs5 Design Premium microsoft office compatible software Purchase Adobe Dreamweaver Cs55 Cheap buy microsoft office pro Purchase Premier System Premier 123 microsoft office xp 2002 Cheap Autocad Electrical 2012 Oem buy microsoft office small business Purchase Adobe Acrobat Professional X Suite discount for microsoft office 2007 Cheap Office 2007 Professional Oem buy microsoft software Best Price Media Composer 5 buy software microsoft office 2008 mac

Hoppla: Da haben wir eine Query, welche 12 Sekunden dauert und auf 6 Tabellen zugreift. Diese Tabellen sind somit während dieser Zeit gelockt. INSERTs und UPDATEs müssen warten. Die auslösenden PHP-Prozesse auf den Webservern hängen, belegen einen Apache-Slot und damit Speicher. Mit der Zeit sind die all 512 Slots belegt, der Speicher gefüllt und der Benutzer hat seine Seite immer noch nicht gesehen.

Not nice.

Die Query (stark vereinfacht):

SELECT name
FROM persons
INNER JOIN regions ON persons.region_id=regions.region_id
WHERE persons.region_id=5 OR regions.country_id=1

“OR-Alarm”!

“OR-Alarm”? Das ist, wenn im WHERE-Statement einer Query ein OR benutzt wird. Und dieses OR verbindet zwei unterschiedliche Felder. Diese beiden Felder können zwar Indexiert sein, aber der Index kann wegen em OR nicht verwendet werden. Andere WHERE-Bedingungen, welche die Verwendung einens Indexes zulassen würden, sind nicht vorhanden. Dies kann man mit dem EXPLAIN SELECT-Kommando leicht überprüfen.

Wenn der Index nicht verwendet werden kann, muss die WHERE-Condition für alle Kombinationen von persons und regions überprüft werden. Bei 50’000 Personen und 100 Regionen sind das 5’000’000 Kombinationen, welche sequenziell durchaufen werden müssen. Das Ergebnis ist klar: Die Abfrage dauert lange 12 Sekunden.

Lässt man die eine Bedingung fallen, verschwindet das OR und der Index auf region_id kann verwendet werden:

SELECT name
FROM persons
INNER JOIN regions ON persons.region_id=regions.region_id
WHERE regions. country_id =1

Ueberprüfung mit EXPLAIN SELECT: Kein kartesisches Produkt mehr notwendig. Kein Table Scan mehr. Nur noch 10ms. Das gleiche gilt, wenn man die andere Bedingung fallen lässt.

Aufgeteilt mehr Leistung

Nun könnte man im Client diese beiden Queries hintereinander ausführen lassen und die resultierenden Zeilen in einem Array sammeln. Ergibt 20ms Ausführungszeit. Aber wenig eleganten Code.

Schöner ist die UNION-Clause: Hier kann man die Ergebnisse von mehreren Queries vereinigen.

SELECT name
FROM persons
INNER JOIN regions ON persons.region_id=regions.region_id
WHERE regions. country_id =1
UNION
SELECT name
FROM persons
WHERE persons.region_id=5

Intern werden nur beide Teilqueries separat ausgeführt. Da die Teilqueries kein OR mehr besitzen, kann der Index verwendet werden. Beide Teilqueries sind somit rasend schnell.

Resultat: Das gleiche wie bei der Query mit dem OR. Ausführungszeit 1000x höher.