Jump to content


Photo

MySql difficult query


  • Please log in to reply
10 replies to this topic

#1 stuffie

stuffie
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 24 February 2006 - 04:49 PM

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... :)
[a href="http://www.stuffie.be" target="_blank"]http://www.stuffie.be[/a]

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 24 February 2006 - 05:04 PM

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) FROM customer a, customer_category b, category_keyword c, customer_category_keyword d
WHERE a.customer_nr = b.customer_nr AND b.category_nr = c.category_nr AND c.keyword_nr = d.keyword_nr AND (customer_name = '$word' || customer_name LIKE '%$word%' || keyword_word = '$word');
[!--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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 stuffie

stuffie
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 24 February 2006 - 05:34 PM

[!--quoteo(post=349071:date=Feb 24 2006, 06:04 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Feb 24 2006, 06:04 PM) View Post[/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) FROM customer a, customer_category b, category_keyword c, customer_category_keyword d
WHERE a.customer_nr = b.customer_nr AND b.category_nr = c.category_nr AND c.keyword_nr = d.keyword_nr AND (customer_name = '$word' || customer_name LIKE '%$word%' || keyword_word = '$word'); [!--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 = "[span style=\'color:blue;font-weight:bold\']SELECT[/span] distinct(Klanten.KlantNr), Klanten.KlantNaam, Categorie_Trefwoord.Trefwoord_Omschrijving

FROM Klanten, Klant_Categorie, Klant_Categorie_Trefwoord, Categorie_Trefwoord

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

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 = "
[span style=\'color:blue;font-weight:bold\']SELECT[/span] customer_category_keyword.customer_nr
FROM customer LEFT JOIN customer_category ON (customer_category.customer_nr= customer.customer_nr)
LEFT JOIN customer_category_keyword ON (customer_category_keyword.customer_nr= customer.customer_nr)
WHERE customer.customer_nameLIKE '%".$word."%'
"; [!--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.
[a href="http://www.stuffie.be" target="_blank"]http://www.stuffie.be[/a]

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 24 February 2006 - 05:34 PM

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.

#5 stuffie

stuffie
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 24 February 2006 - 05:40 PM

[!--quoteo(post=349077:date=Feb 24 2006, 06:34 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 24 2006, 06:34 PM) View Post[/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!!
[a href="http://www.stuffie.be" target="_blank"]http://www.stuffie.be[/a]

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 24 February 2006 - 05:44 PM

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

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 24 February 2006 - 06:02 PM

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:
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
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.

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 24 February 2006 - 06:20 PM

I'd just like to add:

Kneel before Zod!

#9 stuffie

stuffie
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 25 February 2006 - 09:31 AM

Thx a lot dude!

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

[a href="http://www.stuffie.be" target="_blank"]http://www.stuffie.be[/a]

#10 stuffie

stuffie
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 25 February 2006 - 09:46 AM

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
[a href="http://www.stuffie.be" target="_blank"]http://www.stuffie.be[/a]

#11 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 25 February 2006 - 04:30 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users