Hi there, I am using mysql 4.0.18 and do want to do the following:
There is one table with classifieds that contains the title and the text. Another one contains tags associated with the classifieds. Each classified row can have multiple tags inside the tag table. The tag table contains the ad_id which is the ID of the classified table. Without querying the tag table the query takes just 0.000x seconds, but with the tag table it takes over 0.3. Indexes are set properly, as far as I can see. This is my query (involves some other tables as well, but they the performance problem lies with the tags table. SELECT cp . * , UNIX_TIMESTAMP( cl.timestamp ) AS date, cl . * , MATCH ( cl.text, cl.title ) AGAINST ( 'anzug' IN boolean MODE ) AS relevance, ar.area_name, ar.area_name_secure, cs.name_safe AS sub_ns FROM classifieds cl, de_cat_sub cs LEFT JOIN classifieds_pix AS cp ON cl.ID = cp.classified_id LEFT JOIN area_names AS ar ON cl.area_id = ar.ID LEFT JOIN tags AS ta ON ta.ad_id = cl.ID WHERE cl.confirmed =1 AND cl.country = 'de' AND ( ( MATCH ( cl.text, cl.title ) AGAINST ( 'anzug' IN BOOLEAN MODE ) ) OR ta.tag = 'anzug' ) AND ( 1 ) AND cl.cat_id = cs.ID GROUP BY cl.ID ORDER BY relevance DESC , cl.timestamp DESC LIMIT 0 , 20 Doing an explain brings up: table type possible_keys key key_len ref rows Extra cs ALL PRIMARY NULL NULL NULL 53 Using temporary; Using filesort cl ref cat_id,country cat_id 2 cs.ID 85 Using where cp ref album_id album_id 3 cl.ID 1 ar eq_ref PRIMARY PRIMARY 2 cl.area_id 1 ta ref ad_id ad_id 3 cl.ID 2 Using where I am completly lost here. I assume that there is a much better way to include the tags table into the search. Any ideas? Thank you for any help on that. Merlin -- Merlin [EMAIL PROTECTED] -- http://www.fastmail.fm - Faster than the air-speed velocity of an unladen european swallow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
