many many lookup fields

[jez]

Hi everyone,

I have been trawling through the documentation and at various sites and i am confused as to what i need to do in order to resolve lookup fields.

I have a main table with items in it and some fields are simply an ID such as the city_id, which references the city in the city table.
I have about 5 or 6 or these.
My query is as follows...

SELECT *
FROM 
	items i,
	city c, 
	item_price_category p, 
	chapter ch, 
	areas a, 
	index_cat2 c2
WHERE 
i.item_price_cat_id = p.price_cat_id AND
i.item_price_cat_id = p.price_cat_id AND 
i.item_city_id = c.city_id AND 
i.item_chapter_id = ch.chapter_id AND 
i.item_cat2_id = c2.cat2_id AND ....

I have left it open ended as i want to add the specific info there for the query and am simply trying to get all the basic stuff first.

example of extra sql on the end..

c.city_name LIKE '%london%' OR 
a.area_desc LIKE '%london%' 
ORDER BY i.item_name ASC LIMIT 0,5;

When i try and run this query it mysql query browser just hangs, run from within my web page that hangs.
Can anyone help me out with this, as it should be a fairly common task, so i cant see why i am having such problems.

Also can anyone suggest what the INSERT statement format would be which resovles lookups.

Thanks in advance.

Jez

 

[rac2]

Not sure what you mean by

...in order to resolve lookup fields.

I have never experienced any performance issues involving lookup fields, these are very efficient becauset the tables are small, a few dozen rows at most, and the ids are indexed.

The performance issue is more likely related to the LIKE clauses. Possibly also the OR. Try running the query without any lookups and without the lookup tables. Does it complete?

Also try placing the LIKE conditions inside parentheses.

WHERE i.item_price_cat_id = p.price_cat_id 
  AND i.item_price_cat_id = p.price_cat_id
  AND i.item_city_id = c.city_id 
  AND i.item_chapter_id = ch.chapter_id 
  AND i.item_cat2_id = c2.cat2_id 
  AND (
       c.city_name LIKE '%london%' OR
       a.area_desc LIKE '%london%' 
      )

Just some thoughts. Let me know what you find with these two tests.

 

[jez]

Thanks very much for the answer.

I have tried it in MySql query brower and it works very well, so i expect it should work fine in the browser.

As for the lookup work i was talking about, i have solved that . My problem was getting the right syntax for an INSERT with multiple sub queries to link all the ID's that are in the WHERE clause above.

Thanks again.

Jez