MySql difficult query

[stuffie]

I have the following tables...


I've got the following tables

customer(customer_nr, customer_name)

customer_category (customer_nr, category_nr)

category_keyword(category_nr, keyword_nr, keyword_word)

customer_category_keyword(customer_nr, customer_nr, keyword_nr)


A custumor has a costumor_name and is linked to a certain category. This category has also keywords (keyword_word)


The Problem:
---------------

Give a list of all the customers where the name is equal to or contains a part of a given word.

Also there has to be looked in the categories to check if the given word is equal to a keyword. All the customers of this category are also to be listed.

A customer may be listed 1 time en the results must be ordered by customer_name.

Thx a lot guys!
sorry for the bad english... :)

[obsidian]

try something like this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT(customer_name) [color=green]FROM[/color] [color=orange]customer[/color] a, customer_category b, category_keyword c, customer_category_keyword d
[color=green]WHERE[/color] a.customer_nr [color=orange]=[/color] b.customer_nr [color=blue]AND[/color] b.category_nr [color=orange]=[/color] c.category_nr [color=blue]AND[/color] c.keyword_nr [color=orange]=[/color] d.keyword_nr [color=blue]AND[/color] (customer_name [color=orange]=[/color] [color=red]'$word'[/color] || customer_name [color=orange]LIKE[/color] [color=red]'%$word%'[/color] || keyword_word [color=orange]=[/color] [color=red]'$word'[/color]);
[!--sql2--][/div][!--sql3--]

may not be perfect, as it's a little hard to understand exactly what you're after, but i think it should get you on the right track

[stuffie]

[!--quoteo(post=349071:date=Feb 24 2006, 06:04 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Feb 24 2006, 06:04 PM) [snapback]349071[/snapback][/div][div class=\'quotemain\'][!--quotec--]
try something like this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT(customer_name) [color=green]FROM[/color] [color=orange]customer[/color] a, customer_category b, category_keyword c, customer_category_keyword d
[color=green]WHERE[/color] a.customer_nr [color=orange]=[/color] b.customer_nr [color=blue]AND[/color] b.category_nr [color=orange]=[/color] c.category_nr [color=blue]AND[/color] c.keyword_nr [color=orange]=[/color] d.keyword_nr [color=blue]AND[/color] (customer_name [color=orange]=[/color] [color=red]'$word'[/color] || customer_name [color=orange]LIKE[/color] [color=red]'%$word%'[/color] || keyword_word [color=orange]=[/color] [color=red]'$word'[/color]); [!--sql2--][/div][!--sql3--]

may not be perfect, as it's a little hard to understand exactly what you're after, but i think it should get you on the right track
[/quote]

Thx for the effort...

I have got already something like this...

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$query [color=orange]=[/color] "[span style=\'color:blue;font-weight:bold\']SELECT[/span] distinct(Klanten.KlantNr), Klanten.KlantNaam, Categorie_Trefwoord.Trefwoord_Omschrijving

[color=green]FROM[/color] [color=orange]Klanten,[/color] Klant_Categorie, Klant_Categorie_Trefwoord, Categorie_Trefwoord

[color=green]WHERE[/color] Klanten.KlantNr [color=orange]=[/color] Klant_Categorie.KlantNr [color=blue]AND[/color] Klanten.KlantNr [color=orange]=[/color] Klant_Categorie_Trefwoord.KlantNr [color=blue]AND[/color] Klant_Categorie.KlantNr [color=orange]=[/color] Klant_Categorie_Trefwoord.KlantNr [color=blue]AND[/color] Klant_Categorie.CategorieNr [color=orange]=[/color] Categorie_Trefwoord.Categorie_Nummer [color=blue]AND[/color] Klant_Categorie.CategorieNr [color=orange]=[/color] Klant_Categorie_Trefwoord.CategorieNr [color=blue]AND[/color] Klant_Categorie_Trefwoord.CategorieNr [color=orange]=[/color] Categorie_Trefwoord.Categorie_Nummer [color=blue]AND[/color] Categorie_Trefwoord.Trefwoord_Nummer [color=orange]=[/color] Klant_Categorie_Trefwoord.TrefwoordNr [color=blue]AND[/color] (Categorie_Trefwoord.Trefwoord_Omschrijving [color=orange]LIKE[/color] [color=red]'%"[/color].$_SESSION[[color=red]"zoekWoord"[/color]].[color=red]"%'[/color] [color=blue]OR[/color] Klanten.KlantNaam [color=orange]LIKE[/color] [color=red]'%"[/color].$_SESSION[[color=red]"zoekWoord"[/color]].[color=red]"%'[/color])

GROUP BY Klanten.KlantNr"; [!--sql2--][/div][!--sql3--]

The problem with this methode is that it takes very long to execute the query (it's a large database)

Is there a more optimized method?

Now in the meanwhile i get to the following...

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$query [color=orange]=[/color] "
[span style=\'color:blue;font-weight:bold\']SELECT[/span] customer_category_keyword.customer_nr
[color=green]FROM[/color] [color=orange]customer[/color] [color=green]LEFT[/color] [color=green]JOIN[/color] customer_category ON (customer_category.customer_nr[color=orange]=[/color] customer.customer_nr)
[color=green]LEFT[/color] [color=green]JOIN[/color] customer_category_keyword ON (customer_category_keyword.customer_nr[color=orange]=[/color] customer.customer_nr)
[color=green]WHERE[/color] customer.customer_name[color=orange]LIKE[/color] [color=red]'%"[/color].$word.[color=red]"%'[/color]
"; [!--sql2--][/div][!--sql3--]

Now I just have to make sure that he also looks in the keywords of the categories. Now he only search in the name of the customer.

[wickning1]

Keep in mind that, due to the use of LIKE, this query will always be forced to scan through a great number of rows, and will be very slow as the database grows.

You may want to look into substring indexing strategies.

You can also do it in three steps... find all the keywords that match first, then all the customer names that match, then synthesize. That will be quite a bit faster than the single query JOIN.

[stuffie]

[!--quoteo(post=349077:date=Feb 24 2006, 06:34 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 24 2006, 06:34 PM) [snapback]349077[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You can also do it in three steps... find all the keywords that match first, then all the customer names that match, then synthesize. That will be quite a bit faster than the single query JOIN.
[/quote]

Can you compose it please, so I get what you mean.

Thx a lot!!

[wickning1]

I'll work on it, check back in a bit.

[wickning1]

This should do the trick. I'm not clear on whether the keyword search should be a substring search or not, so I used LIKE. This query would go even faster if you could change it to = instead of LIKE.

Here's the query:
[code]SELECT customer_nr FROM customer c
LEFT JOIN (
    SELECT customer_nr FROM customer_category_keyword
    INNER JOIN (
        SELECT keyword_nr FROM category_keyword
        WHERE keyword_word LIKE "%searchStr%"
    ) cats USING(keyword_nr)
    GROUP BY customer_nr
) keys USING (customer_nr)
WHERE c.customer_name LIKE "%searchStr%"
OR keys.customer_nr IS NOT NULL
ORDER BY c.customer_name[/code]
Please note that this is still going to be slow when you have a lot of customers. Maintaining your own custom index would be the best solution.

Edit: I just corrected an error.
Edit: Made another improvement.

[wickning1]

I'd just like to add:

Kneel before Zod!

[stuffie]

Thx a lot dude!

I will try it today... I will keep you updated!

[stuffie]

problem...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keys USING(customer_nr) WHERE c.customer_name = '%word%' OR keys.customer_nr IS NOT NULL' at line 9

[wickning1]

Heh whoops, keys is a reserved word, use mykeys.