run away query 1

[jez]

Hi everyone,

I am trying to write a search query for a keyword search on a web site.

I want to search through a few different tables and get just one result set back. i.e. i dont want to do multiple queries.

I am currently trying to do this with JOINs. I think i have done something wrong as i just lock up my local server when i run this query. Any suggestions would be great.
I think i need LEFT JOINS but i dont think i am doing it correcly.

SELECT `area`.area1_desc, `area`.area2_desc, `city`.city_name, `features`.feature_title, `item_price_category`.price_cat_desc, `items`.item_name, `items`.item_bookstandfirst, `items`.item_url, `items`.item_AZcaption, `items`.item_AreaCaption, `items`.item_TypeCaption, `items`.item_goodValue, `practical_index`.pIndex_desc, `practicalities`.p_name, `travel`.travel_name, `useful_links`.link_desc 
FROM `chapter` LEFT JOIN `city` ON `chapter`.chapter_id = `city`.city_id 
LEFT JOIN `items` ON `chapter`.chapter_id = `items`.item_chapter_id 
LEFT JOIN `calendar` ON `city`.city_id = `calendar`.entry_city_id 
LEFT JOIN `practicalities` ON `city`.city_id = `practicalities`.p_city_id 
LEFT JOIN `practical_index` ON `practical_index`.pIndex_id = `practicalities`.p_indexCat_id 
LEFT JOIN `travel` ON `city`.city_id = `travel`.travel_city_id 
LEFT JOIN `features` ON `city`.city_id = `features`.feature_city_id LEFT JOIN `useful_links` ON `chapter`.chapter_id = `useful_links`.link_chapter_id LEFT JOIN `item_price_category` ON `item_price_category`.price_cat_id = `items`.item_price_cat_id 
LEFT JOIN `area` ON `area`.area_id = `items`.item_area2_id WHERE 
`area`.area1_desc = '%fish%' OR 
`area`.area2_desc = '%fish%' OR 
`chapter`.chapter_lname = '%fish%' OR 
`city`.city_name = '%fish%' OR 
`features`.feature_title = '%fish%' OR `item_price_category`.price_cat_desc = '%fish%' OR `items`.item_name = '%fish%' OR 
`items`.item_goodValue = '%fish%' OR `practical_index`.pIndex_desc = '%fish%' OR `practicalities`.p_name = '%fish%' OR 
`travel`.travel_name = '%fish%' OR 
`useful_links`.link_desc = '%fish%' 
LIMIT 0,100;

In the code above "fish" is my keyword entered from the text box on site.


Thanks in advance for any tips.

 

[r937]

as far as i can make out, these are your table relationships --

chapter - useful_links
chapter - items - area
chapter - items - item_price_category
chapter - city
chapter - calendar
chapter - travel
chapter - features
chapter - practicalities - practical_index

it would be decidedly helpful (for you, not for us) if you would write your joins in the order that the relationships exist

for performance, at a minumum, each of the columns used in the joins should either be the primary key, or have an index declared on it

even if the query were to finish executing, it will not return any rows because you are not testing the wildcard search string correctly -- you want LIKE, not equality

as for results, try moving the predicates out of the WHERE clause (which, by the way, have the undesireable effect of negating the efforts of the LEFT OUTER JOINs, in effect making them return the exact same results as if you had written INNER JOINs)

like this --

SELECT area.area1_desc
     , area.area2_desc
     , city.city_name
     , features.feature_title
     , item_price_category.price_cat_desc
     , items.item_name
     , items.item_bookstandfirst
     , items.item_url
     , items.item_AZcaption
     , items.item_AreaCaption
     , items.item_TypeCaption
     , items.item_goodValue
     , practical_index.pIndex_desc
     , practicalities.p_name
     , travel.travel_name
     , useful_links.link_desc 
  FROM chapter 
LEFT
  JOIN useful_links
    ON useful_links.link_chapter_id = chapter.chapter_id  
   AND useful_links.link_desc LIKE '%fish%' 
LEFT
  JOIN items
    ON items.item_chapter_id  = chapter.chapter_id  
   AND (
       items.item_name LIKE '%fish%'
    OR items.item_goodValue LIKE '%fish%'
       ) 
LEFT
  JOIN area
    ON area.area_id = items.item_area2_id    
   AND (
       area.area1_desc LIKE '%fish%'
    OR area.area2_desc LIKE '%fish%'
       ) 
LEFT
  JOIN item_price_category
    ON item_price_category.price_cat_id = items.item_price_cat_id 
   AND item_price_category.price_cat_desc LIKE '%fish%'
LEFT
  JOIN city
    ON city.city_id   = chapter.chapter_id
   AND city.city_name LIKE '%fish%'
LEFT
  JOIN calendar
    ON calendar.entry_city_id     = city.city_id
LEFT
  JOIN travel
    ON travel.travel_city_id      = city.city_id
   AND travel.travel_name LIKE '%fish%'
LEFT
  JOIN features
    ON features.feature_city_id   = city.city_id
   AND features.feature_title LIKE '%fish%'
LEFT
  JOIN practicalities
    ON practicalities.p_city_id   = city.city_id
   AND practicalities.p_name LIKE '%fish%'
LEFT
  JOIN practical_index
    ON practical_index.pIndex_id = practicalities.p_indexCat_id 
   AND practical_index.pIndex_desc LIKE '%fish%'
 WHERE chapter.chapter_lname LIKE '%fish%'

now, wouldn't you say that this is a lot easier to understand and perform maintenance on?




r937.com | rudy.ca

 

[jez]

Wow, thanks that looks great, I will try and incorporate that in now and see how it runs.

Also i think i have got more of an understanding of how joins work from you post, so thanks again.

jez

 

[jez]

Yes that worked great, thank you very much. I see where i was going wrong. I guess you have to take it one step (or JOIN) at a time then the overall query is not so complex.

Thank you again,

Jez

 

[guelphdad]

it is also easier, when written Rudy's way, to see when you have left out a condition, or as he pointed out, changed an OUTER JOIN to an INNER JOIN merely by putting your search conditions in the wrong location in your query.