MYSQL - query traag- niet optimaal geschreven?

[Verwijderd]

Voor een bedrijvengids *snip* loopt een query erg traag. Soms wel 30 seconden.
Het is wel een belangrijke query, om bedrijven uit de database te selecteren onder enkele voorwaarden.

Ben benieuwd of er iets mis is of niet optimaal geschreven.

Tabellen koppelen doe ik altijd met left join. Kan dat misschien anders?

Hopelijk kan iemand ons helpen, want een trage website hebben we natuurlijk niks aan!

Hieronder de query.

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

SQL:

select
    SQL_CALC_FOUND_ROWS
    distinct
        a.id,
        a.bedrijfsnaam,
        a.straat,
        a.huisnummer,
        a.postcode_cijfers,
        a.postcode_toevoeging,
        a.plaats,
        a.overzicht_omschrijving,
        a.overzicht_afbeelding,
        a.ind_gratis_vermelding,
        b.categorie,
        c.hoofdcategorie
    from
        bedrijven a
        left join bedrijven_categorieen b ON a.id_bcategorie = b.id
        left join bedrijven_hoofdcategorieen c ON b.id_hoofdcategorie = c.id
        left join bedrijven_trefwoorden d ON d.id_bedrijf = a.id
    where
        c.hoofdcategorie = "Huis"
        and
        (
            a.bedrijfsnaam like "%schilder%"
            or a.overzicht_omschrijving like "%schilder%"
            or d.trefwoord like "%schilder%"
        )
        and a.ind_actief = "Y"
        and a.ind_zichtbaar_in_zoekmachine = "Y"
    order by
        a.ind_gratis_vermelding asc, rand(18)
    limit 0,15

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

[ Voor 0% gewijzigd door Creepy op 14-05-2008 18:04 ]

[Johnny]

Wat voor indexen heb je op de tabellen?

[Verwijderd]

Iedere tabel heeft een ID veld, gemarkeerd als primary key.

Verder hebben sommige tabellen, bijvoorbeeld bedrijven_trefwoorden, een id_bedrijf veld, om te koppelen aan de tabel bedrijven. Deze id_ velden heb ik allemaal een standaard index gegeven.

[eamelink]

Geef eens een wat completer schema van je database, inclusief de indices. En stop je code tussen [code=sql] [/] blokken.

Je kan een EXPLAIN voor je select zetten, dan zie je hoe MySQL de query aanpakt en of er indexen gebruikt worden. Zo niet; maak de juist indexen aan.

Verder is

code:

SQL_CALC_FOUND_ROWS

een notoire traagmaker. Het is vaak sneller om eerst een query met LIMIT te doen, en daarna nog een keer de query maar dan met COUNT en zonder de sortering.

Sorteren op rand() is trouwens ook traag, en LIKE's zijn ook traag.

Het zijn inderdaad wel alle ingrediënten voor een trage query

Als ik het hier zo zou moeten inschatten dan zou een index op hoofdcategorie, ind_actief, ind_zichtbaar_in_zoekmachine en ind_gratis_vermelding (dus één index op die vier velden) wel moeten helpen, maar met een EXPLAIN uitslag erbij is dat wat makkelijker te bepalen

[ Voor 17% gewijzigd door eamelink op 14-05-2008 11:02 ]

[bstudio]

Joins zijn over het algemeen wel vertragend, maar zouden niet de beperkende factor moeten zijn. Is 30 seconden standaard of is het alleen een uitschieter? Wat zijn de gemiddelde tijden?

[Verwijderd]

De database opzet zal ik zo posten, export genoeg?

Verder had ik de query eerst met limit gemaakt, was nog traag, daarna juist met SQL_CALC_FOUND_ROWS gemaakt.

RAND(nummer) zorgt ervoor dat iedere browser sessie een nieuwe volgorde van het bedrijvenresultaat hanteert.

En is er een alternatief voor LIKE wat sneller is?

[Verwijderd]

Momenteel 0.1780 sec.

Echte uitschieters zijn 30 seconden, verder komt vaak 3 seconden voor.

Maar toch vreemd dat het zo lang moet duren. Weet ook niet of dit ligt aan bezoekersaantallen.

Lastig te testen.

[bigbeng]

Je kunt een full-text index combineren met MATCH in je query. Zie hier voor meer info.

[Verwijderd]

SQL:

CREATE TABLE `bedrijven` (
  `id` int(11) NOT NULL auto_increment,
  `klantnummer` int(11) default NULL,
  `bedrijfsnaam` varchar(255) default NULL,
  `gebruikersnaam` varchar(255) default NULL,
  `wachtwoord` varchar(255) default NULL,
  `naam_contactpersoon` varchar(255) default NULL,
  `id_bcategorie` int(11) default NULL,
  `andere_categorie` varchar(255) default NULL,
  `straat` varchar(255) default NULL,
  `huisnummer` varchar(150) default NULL,
  `postcode_cijfers` varchar(150) default NULL,
  `postcode_toevoeging` varchar(5) default NULL,
  `plaats` varchar(255) default NULL,
  `telefoonnummer` varchar(255) default NULL,
  `mobiel` varchar(150) default NULL,
  `faxnummer` varchar(255) default NULL,
  `emailadres` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `ind_gratis_vermelding` varchar(5) default NULL,
  `ind_overzicht_afbeelding` varchar(5) default NULL,
  `overzicht_afbeelding` varchar(255) default NULL,
  `ind_overzicht_omschrijving` varchar(5) default NULL,
  `overzicht_omschrijving` longtext,
  `ind_presentatie_pagina` varchar(5) default NULL,
  `presentatie_pagina` longtext,
  `ind_snel_selecteren` varchar(5) default NULL,
  `snel_selecteren_omschrijving` varchar(50) default NULL,
  `ind_zichtbaar_in_zoekmachine` varchar(5) default NULL,
  `max_aantal_producten` int(11) default NULL,
  `aantal_trefwoorden` int(11) default NULL,
  `ind_banner_rechts` varchar(5) default NULL,
  `lid_sinds` date default NULL,
  `ind_actief` varchar(5) default NULL,
  `gebeld_op` date default NULL,
  `terugbellen_op` date default NULL,
  `promootmail_verstuurd_op` date default NULL,
  `ind_interesse` varchar(5) default NULL,
  `ind_akkoord` varchar(5) default NULL,
  `ind_welkomstmail_verzonden` varchar(5) default NULL,
  `id_marketeer` int(11) default NULL,
  `opmerking` longtext,
  `opmerking_marketeer` longtext,
  `opmerking_allesvoorjehuis` longtext,
  `datum_overeenkomst_verstuurd` date default NULL,
  `datum_overeenkomst_ontvangen` date default NULL,
  PRIMARY KEY  (`id`),
  KEY `ind_gratis_vermelding` (`ind_gratis_vermelding`)
) ENGINE=MyISAM AUTO_INCREMENT=12238 DEFAULT CHARSET=latin1 AUTO_INCREMENT=12238 ;

CREATE TABLE `bedrijven_categorieen` (
  `id` int(11) NOT NULL auto_increment,
  `id_hoofdcategorie` int(11) default NULL,
  `categorie` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id_hoofdcategorie` (`id_hoofdcategorie`)
) ENGINE=MyISAM AUTO_INCREMENT=105 DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;

CREATE TABLE `bedrijven_hoofdcategorieen` (
  `id` int(11) NOT NULL auto_increment,
  `hoofdcategorie` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE `bedrijven_trefwoorden` (
  `id` int(11) NOT NULL auto_increment,
  `id_bedrijf` int(11) default NULL,
  `trefwoord` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id_bedrijf` (`id_bedrijf`)
) ENGINE=MyISAM AUTO_INCREMENT=228 DEFAULT CHARSET=latin1 AUTO_INCREMENT=228 ;

[MueR]

Waarom maak je gebruik van strings (Y/N) voor booleans? Gebruik daar eens gewoon integers voor. Ook voor de selectie van je hoofdcategorie doe je dit. Is daar een reden voor? Ik neem tenminste aan dat het via een formulier binnenkomt, dan kan je toch gewoon een id meegeven voor dit soort dingen? Nu moet mysql een hoeveelheid aan text comparisons doen, dat helpt ook niet echt.

[Verwijderd]

Goed punt van de Y/N, is niet echt een reden voor. Ooit mee begonnen, maar blijkt dat er meerdere punten zijn die onderuit gaan met een flink ingevulde database.

Ik zal ook dit punt eens gaan testen zodra de website weer traag wordt.

Kom ik op terug.

[Verwijderd]

bigbeng schreef op woensdag 14 mei 2008 @ 11:09:
Je kunt een full-text index combineren met MATCH in je query. Zie hier voor meer info.

SQL:

            MATCH
                (a.bedrijfsnaam) AGAINST('[[:<:]]schilder[[:>:]]' IN BOOLEAN MODE)
                or
                MATCH
                (a.overzicht_omschrijving) AGAINST('[[:<:]]schilder[[:>:]]' IN BOOLEAN MODE)
                or
                MATCH
                (d.trefwoord) AGAINST('[[:<:]]schilder[[:>:]]' IN BOOLEAN MODE)

Hoe kan ik nu laten zoeken op %schilder% ipv 'schilder'? Dit om de Match functie ipv Like te gebruiken.

[bigbeng]

Waarom zou je op %schilder% willen zoeken? Match doet al een full text search op "schilder" dus overal waar hij voorkomt in een tekst.

Je kunt volgens mij ook een fulltext index aanleggen op meerdere velden in 1x, maar het fijne weet ik daar niet van.

[Verwijderd]

bigbeng schreef op woensdag 14 mei 2008 @ 11:54:
Waarom zou je op %schilder% willen zoeken? Match doet al een full text search op "schilder" dus overal waar hij voorkomt in een tekst.

Je kunt volgens mij ook een fulltext index aanleggen op meerdere velden in 1x, maar het fijne weet ik daar niet van.

Maar als men zoekt op 'schilde' (stel dat) dan moet het bedrijf met de trefwoord 'schilder' gevonden worden.

In deze query gebeurt dat niet.

SQL:

select
    SQL_CALC_FOUND_ROWS
    distinct
        a.id,
        a.bedrijfsnaam,
        a.straat,
        a.huisnummer,
        a.postcode_cijfers,
        a.postcode_toevoeging,
        a.plaats,
        a.overzicht_omschrijving,
        a.overzicht_afbeelding,
        a.ind_gratis_vermelding,
        b.categorie,
        c.hoofdcategorie
    from
        bedrijven a
        left join bedrijven_categorieen b ON a.id_bcategorie = b.id
        left join bedrijven_hoofdcategorieen c ON b.id_hoofdcategorie = c.id
        left join bedrijven_trefwoorden d ON d.id_bedrijf = a.id
    where
        c.hoofdcategorie = "Huis"
        and
        (
                a.bedrijfsnaam RLIKE '[[:<:]]schilder[[:>:]]'
                or
                a.overzicht_omschrijving RLIKE '[[:<:]]schilder[[:>:]]'
                or
                d.trefwoord RLIKE '[[:<:]]schilder[[:>:]]'
        )
        and a.ind_actief = "Y"
        and a.ind_zichtbaar_in_zoekmachine = "Y"
    order by
        a.ind_gratis_vermelding asc, rand(18)
    limit 0,15

[ Voor 0% gewijzigd door Creepy op 14-05-2008 18:04 ]

[frickY]

Dat hij de ene keer 0.17 en de andere keer 30 seconden nodig heeft, zegt mij dat hij een tijdelijke tabel en een filesort gebruikt. Dat gaat goed tot de query 2 of meer keer tegelijkertijd moet worden uitgevoerd.

@bigbeng
MATCH AGAINST zoekt woorden, geen woorddelen. "bedrijfschilder" wordt niet gevonden met een AGAINST("schilder"). Dat kan ook helemaal niet, zo is een FULL TEXT index niet opgebouwd.
Een FULL TEXT index kan inderdaad over meerdere kolommen, maar wel slechts binnen 1 tabel.

[ Voor 10% gewijzigd door frickY op 14-05-2008 12:02 ]

[Verwijderd]

Verwijderd schreef op woensdag 14 mei 2008 @ 11:59:
[...]

Sorrie, verkeerde code gepost.

---

Maar als men zoekt op 'schilde' (stel dat) dan moet het bedrijf met de trefwoord 'schilder' gevonden worden.

In deze query gebeurt dat niet.

SQL:

SELECT 
SQL_CALC_FOUND_ROWS DISTINCT a.id, a.bedrijfsnaam, a.straat, a.huisnummer, a.postcode_cijfers, a.postcode_toevoeging, a.plaats, a.overzicht_omschrijving, a.overzicht_afbeelding, a.ind_gratis_vermelding, b.categorie, c.hoofdcategorie
FROM bedrijven a
LEFT JOIN bedrijven_categorieen b ON a.id_bcategorie = b.id
LEFT JOIN bedrijven_hoofdcategorieen c ON b.id_hoofdcategorie = c.id
LEFT JOIN bedrijven_trefwoorden d ON d.id_bedrijf = a.id
WHERE c.hoofdcategorie = "Huis"
AND (

MATCH (
a.bedrijfsnaam
)
AGAINST (
'[[:<:]]schilde[[:>:]]'
IN BOOLEAN
MODE 
)
OR 
MATCH (
a.overzicht_omschrijving
)
AGAINST (
'[[:<:]]schilde[[:>:]]'
IN BOOLEAN
MODE 
)
OR 
MATCH (
d.trefwoord
)
AGAINST (
'[[:<:]]schilde[[:>:]]'
IN BOOLEAN
MODE 
)
)
AND a.ind_actief = "Y"
AND a.ind_zichtbaar_in_zoekmachine = "Y"
ORDER BY a.ind_gratis_vermelding ASC , rand( 18 ) 
LIMIT 0 , 15 

[Verwijderd]

frickY schreef op woensdag 14 mei 2008 @ 12:01:
Dat hij de ene keer 0.17 en de andere keer 30 seconden nodig heeft, zegt mij dat hij een tijdelijke tabel en een filesort gebruikt. Dat gaat goed tot de query 2 of meer keer tegelijkertijd moet worden uitgevoerd.

@bigbeng
MATCH AGAINST zoekt woorden, geen woorddelen. "bedrijfschilder" wordt niet gevonden met een AGAINST("schilder"). Dat kan ook helemaal niet, zo is een FULL TEXT index niet opgebouwd.
Een FULL TEXT index kan inderdaad over meerdere kolommen, maar wel slechts binnen 1 tabel.

Okee duidelijk.

Is er iets aan te doen om de traagheid van een tijdelijke tabel en filesort op te lossen als de query 2 of meer keer tegelijkertijd wordt uitgevoerd?

[bigbeng]

frickY schreef op woensdag 14 mei 2008 @ 12:01:
...
@bigbeng
MATCH AGAINST zoekt woorden, geen woorddelen. "bedrijfschilder" wordt niet gevonden met een AGAINST("schilder"). Dat kan ook helemaal niet, zo is een FULL TEXT index niet opgebouwd.
...

Ik had even niet goed over de partial matches nagedacht. I stand corrected .

TS: wat gaf je EXPLAIN van de query precies terug?

[ Voor 25% gewijzigd door bigbeng op 14-05-2008 12:21 ]

[Verwijderd]

bigbeng schreef op woensdag 14 mei 2008 @ 12:20:
[...]

Ik had even niet goed over de partial matches nagedacht. I stand corrected .

TS: wat gaf je EXPLAIN van de query precies terug?

SQL:

EXPLAIN SELECT 
SQL_CALC_FOUND_ROWS DISTINCT a.id, a.bedrijfsnaam, a.straat, a.huisnummer, a.postcode_cijfers, a.postcode_toevoeging, a.plaats, a.overzicht_omschrijving, a.overzicht_afbeelding, a.ind_gratis_vermelding, b.categorie, c.hoofdcategorie
FROM bedrijven a
LEFT JOIN bedrijven_categorieen b ON a.id_bcategorie = b.id
LEFT JOIN bedrijven_hoofdcategorieen c ON b.id_hoofdcategorie = c.id
LEFT JOIN bedrijven_trefwoorden d ON d.id_bedrijf = a.id
WHERE c.hoofdcategorie = "Huis"
AND (
a.bedrijfsnaam LIKE '%schilde%'
OR a.overzicht_omschrijving LIKE '%schilde%'
OR d.trefwoord LIKE '%schilde%'
)
AND a.ind_actief = "Y"
AND a.ind_zichtbaar_in_zoekmachine = "Y"
ORDER BY a.ind_gratis_vermelding ASC , rand( 18 ) 
LIMIT 0 , 15

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL NULL NULL NULL NULL 12113 Using where; Using temporary; Using filesort
1 SIMPLE b eq_ref PRIMARY,id_hoofdcategorie PRIMARY 4 22386root.a.id_bcategorie 1 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 22386root.b.id_hoofdcategorie 1 Using where
1 SIMPLE d ref id_bedrijf id_bedrijf 5 22386root.a.id 5 Using where; Distinct

[bigbeng]

Ik ben wel benieuwd naar de grootte van je resultset waar de filesort overheen gaat. Gooi de distinct eens weg en de limit. Hoeveel resultaten zitten er dan in je query?

[Verwijderd]

bigbeng schreef op woensdag 14 mei 2008 @ 14:09:
Ik ben wel benieuwd naar de grootte van je resultset waar de filesort overheen gaat. Gooi de distinct eens weg en de limit. Hoeveel resultaten zitten er dan in je query?

Zonder limit en distinct zijn het er 473, zonder where zijn het er 12113.

[Creepy]

Met 12113 rows die met joins uitkomen op 22386 mogelijke rows krijg je queries die langer dan 30 seconden duren? In wat voor velden ben je aan het zoeken dan? Text of fulltext met een shitload aan text erin? Zo ja, dan kan je toch echt beter aan de slag met de fulltext search optie of met een externe search engine, dan gaat een %like% je niet helpen aangezien je dan geen indexen kan gebruiken.

offtopic:
Ik heb gelijk je eerste posts wat opgeleukt met code tags. Ook heb ik je sitenaam uit je startpost verwijdert, het voegt niks toe aan het topic hier waardoor het op reclame lijkt

Zou je overigens voor in de toekomst eens willen kijken naar Welkom in Programming - FAQ en Beleid? Als je meer info had gegeven had er minder om gevraagd hoeven worden. Daarnaast is je probleem dumpen en hopen dat wij het voor je gaan oplossen zonder dat je zelf enige moeite lijkt te doen not done. Vandaar dat we hier vragen om meer info zodat we ook kunnen zien dat je er zelf al actief mee bezig lijkt te zien, en sorry, maar dat lijkt nu totaal niet het geval.

[ Voor 46% gewijzigd door Creepy op 14-05-2008 18:12 ]

[Verwijderd]

Creepy schreef op woensdag 14 mei 2008 @ 18:08:
Met 12113 rows die met joins uitkomen op 22386 mogelijke rows krijg je queries die langer dan 30 seconden duren? In wat voor velden ben je aan het zoeken dan? Text of fulltext met een shitload aan text erin? Zo ja, dan kan je toch echt beter aan de slag met de fulltext search optie of met een externe search engine, dan gaat een %like% je niet helpen aangezien je dan geen indexen kan gebruiken.

offtopic:
Ik heb gelijk je eerste posts wat opgeleukt met code tags. Ook heb ik je sitenaam uit je startpost verwijdert, het voegt niks toe aan het topic hier waardoor het op reclame lijkt

Zou je overigens voor in de toekomst eens willen kijken naar Welkom in Programming - FAQ en Beleid? Als je meer info had gegeven had er minder om gevraagd hoeven worden. Daarnaast is je probleem dumpen en hopen dat wij het voor je gaan oplossen zonder dat je zelf enige moeite lijkt te doen not done. Vandaar dat we hier vragen om meer info zodat we ook kunnen zien dat je er zelf al actief mee bezig lijkt te zien, en sorry, maar dat lijkt nu totaal niet het geval.

Ik moet je eerlijk aangeven dat ik de FAQ niet heb doorgelezen voordat ik dit topic heb gestart. Excuses hiervoor. Verder heb ik alle geboden oplossingen genoteerd, en ga ik snel testen zodra de queries weer gigantisch traag zijn, heeft volgens mij ook te maken met bezoekers aantallen.

Ik zal eens nadenken of we de fulltext search optie er in kunnen verwerken. Moet mogelijk zijn.

[Verwijderd]

Heb zelf slechte ervaringen met meerder joins in mysql. Bij een klein aantal record sets was het nog snel maar later naar dat we er meer als 150.000 in hadden staan werd het erg traag.
Helaas toen source code aangepast en een query laten uitvoeren aan de hand van het resultaat van de eerste query is +- 20-30 x sneller wij zaten ook op 30-40 sec nu weer onder de seconde meestal.

[Creepy]

@linux_freak: Heb je daar ook een concreet voorbeeld van? Want je kan nu wel zeggen dat meerdere joins traag zijn en de boel in code oplossen beter werkt maar ik kan me er eerlijk gezegd erg weinig bij voorstellen. Sterker nog: vaak is het in code oplossen trager dan je DB het echte zoekwerk laten doen omdat een DB daar nu juist voor is gemaakt.

[AtleX]

Verwijderd schreef op donderdag 15 mei 2008 @ 19:01:
Heb zelf slechte ervaringen met meerder joins in mysql. Bij een klein aantal record sets was het nog snel maar later naar dat we er meer als 150.000 in hadden staan werd het erg traag.
Helaas toen source code aangepast en een query laten uitvoeren aan de hand van het resultaat van de eerste query is +- 20-30 x sneller wij zaten ook op 30-40 sec nu weer onder de seconde meestal.

Zeker weten dat je niet gewoon te maken had met slecht geplaatste indexen? Ik join zonder problemen meerdere tabellen met elk ruim meer dan 200.000 records binnen een seconde. En hoe groot denk je dat de tabellen hier van GoT zijn? 150.000 records is niet zo gek veel hoor.

[ Voor 7% gewijzigd door AtleX op 15-05-2008 19:58 ]

[Verwijderd]

Eigenlijk heel simpel. Wanneer je een LIKE begint met '%' kan geen enkele database een index gebruiken, en wanneer je dat ook nog 's over 3 tabellen gaat herhalen wordt 't exponentioneel slechter.
Full text search kan wel wat schelen, maar dan moet je je queries opsplitsen omdat 'ie niet van joins houdt.