Någon som är haj på MySQL-optimering ?

[s9d8as0vjk]

Jag skulle behöva lite hjälp med optimering av en MySQL-databas ... har fixat en prisfunktion för fiskeprylar men vissa av frågorna tar en jäklamassa tid och skulle således behövas förbättras en del.

Så, finns det någon här som är vass på detta med optimering som orkar hjälpa mig med detta ? En liten belöning utlovas om ni kan hjälpa mig så att det rockar.

Det som behöver optimeras finns här:
http://www.fiskesnack.com/productsearch/

Jag får stora problem när man ska söka på ex en butiks samtliga produkter. Så länge man söker på en kategori så rullar det hyfsat men inte perfekt ...

Det som behöver ordnas till är förmodligen själva SQL-frågorna samt att Index måste läggas till på rätt sätt i databasen. Jag har försökt fixa det själv men jag är inte tillräckligt smart tyvärr.

Skicka ett PM om du känner dig manad.

 

[Santo]

Skicka upp dina tabeller där man kan se nycklar och kopplingar mellan dessa. Om jag får tid (vilket jag har väldigt lite till övers av) tittar jag på det.

 

[jayjay]

Skapa index för alla kolumnfält som du använder dig av i frågorna, undvik OR-logik i WHERE-satser.

MySQL har en väldigt bra funktion för att se hur index används i frågor, skriv EXPLAIN framför din SELECT-sats så kommer mysql spotta ur sig, vilka index den använder för att optimera, hur många rader som matchar.

Tänk på att index för varchar, text och dylika fält, endast kan matcha från första tecknet och framåt, allts en

fieldname like '%foo%' kommer aldrig kunna använda ett index, men däremot
fieldname like 'foo%' kommer kunna använda sig av ett index.

Om du prompt måste använda dig av OR-logik, försök använda dig av flera SELECT-frågor kombinerade med UNION, tex

SELECT id, ean, name FROM foo WHERE id = 1234
UNION
SELECT id, ean, name FROM foo WHERE ean = 1234

Frågan ovan är ekvivalent med,
SELECT id, ean, name FROM foo WHERE id = 1234 OR ean = 1234
Men kommer vara sjukt mycket snabbare på en stor tabell som innehåller index för fälten id och ean.

 

[s9d8as0vjk]

Jag har lagt upp en detaljerad fråga på www.mysql.com

Direktlänk:
http://forums.mysql.com/read.php?115,111972,111972#msg-111972

Kika gärna och kolla om ni kan hjälpa mig.

 

[jayjay]

Börja med att skapa ett index för kolumnen:

sProductName

i tabellen:

tblpricecategory

Detta kommer fixa så din left join kan använda index vid joining.

Och du har skapat väldigt knasiga index. När ett index består av flera kolumner, så kommer det första fältet som anges, vara det första som kommer gå att matcha.

Tex, så fyller index:
primary (id) och
Test_Index2 (Id, sProductName, sCategoryName, sManufacturerName)

Samma funktion, dvs den sistnämnda kommer gå att användas vid sökning på Id-fältet, samtidigt som det sistnämnda indexet annars endast blir användbart om du söker på tex:

WHERE Id = 1 AND sProductName = 'foo' AND sCategoryName = 'bar'

Du ska fråga dig själv om du nånsin kommer kombinera en Id = 1 ihop med några andra matchningar. Om inte, så bör du göra om detta index.

Sedan, som någon sagt på forumet, så kan du få problem med indexanvändning om du inte matchar variabeltyperna. Tex, om du söker i ett varchar-fält, med en integer. Detta är oftast bara ett problem vid joins, och inte tillämpligt i ditt fall.

I din tabell, tblpricedata, har due tt index döpt till :

Test_Index1

Som består av 9(!) kolumner! Kommer garanterat inte hjälpa dig. Du bör börja med att skapa index för enskilda kolumner, innan du börjar kombinera flera kolumner i ett index.

MySQL (och förmodligen många andra databaser) kan bara använda ETT index per fråga, vilket får den att välja det index som resulterar i minst rader att behöva utöka sökningen i. Därför är det ibland nyttigt att göra multikolumnsindex. Men utan att veta vad de innehåller för data, och vilka frågor som är vanligast, så är det också omöjligt att direkt säga, hur du ska optimera dina tabeller.

Jag hoppas du iaf har fått en fingervisning i hur det fungerar.

 

[s9d8as0vjk]

Halloj igen,

nu har jag fixat och trixat lite med indexen och samt ändrat varchar(80) till varchar(100) så dom matchar varandra och nu rullar sökfunktionen mycket snabbare även om det säkert finns en hel del att göra fortfarande.

Har postat lite mer detaljerad info här:
http://forums.mysql.com/read.php?115,111972,112170#msg-112170

Har du mera tips så tar jag väldigt tacksamt emot dom även om farten på sökmotorn nu är acceptabel.

 

[jayjay]

Som du ser, i båda dina EXPLAINS så indikerar den att den använder index för joinsen, det är mycket bra.

Däremot kommer din:

WHERE tblPriceData.sProductTitle LIKE "%ambassadeur%"

aldrig kunna använda något index, just eftersom det är en wildcardsökning åt båda hållen. Enda like-sökningen som du kan använda index på är

LIKE 'ambassadeur%'

förutsatt att du har ett index på det fältet. För att minska storleken på indexet anger man oftast hur många tecken in i fältet som man ska indexera på, tex 4. Vilket gör att ovanstående fråga skulle ta ut alla rader som börjar på strängen:

'amba' för att minska de rader som de faktiskt måste matcha hela strängen 'ambassadeur' mot.

I den andra frågan, används inte heller något index, men detta beror på att du inte har någon WHERE-sats, och därför ska den ta ut hela tabellen. Men i JOIN-en så används indexet som den ska.

Man kan använda sig av fulltext-index för att kunna matcha hela ord i en sträng, men då kommer den bara kunna matcha HELA ord, och inte delar av ord. Och då använder man heller inte LIKE-syntaxen. Just sådan form av fritextsökning som du sysslar med, är alltid knepig att optimera. Ett enkelt knep för att snabba upp saker och ting, är att bryta upp meningarna/namnen till enstaka ord, och stoppa i en separat tabell, som har en koppling till ursprungsraden, då kan man möjliggöra sökning på inledande ord med bra index. tex, om du har en sträng som består av:

the quick brown fox jumps over the lazy dog

Så skulle du dela upp det på 9 rader i en separat "ord"-tabell:

tOrd
id | ord
-----------------
123 | the
123 | quick
123 | brown
123 | fox
123 | jumps
123 | over
123 | the
123 | lazy
123 | dog
-----------------

Och sedan skapa ett index på ord (för typ de första 4-5 tecknen), sedan köra WHERE ord like 'bro%' vid själva sökningen, sedan joina det mot själva huvudtabellen och gruppa på dess id.

Svårt att förklara i text.