Happy New Year Mike- JOIN USING(colId) and or JOIN ON(table1.colId=table2.colIdentifier) will preserve the use of Indexes which is faster
LIKE casts off Indexes and does a FTS HTH Martin Any recommendations on non-PHP Forum Software for Apache? ______________________________________________ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Thu, 1 Jan 2009 11:28:51 +0800 > From: mik...@qualityadvantages.com > To: mysql@lists.mysql.com > Subject: Can a JOIN statement do this? > > Hello mysql and Happy New Year, > > I am working with a Forum database. It contains a forums table, a > posts table and a threads table. Some of the posts contain flash > objects that I can find using a query like this one: > > SELECT `pagetext`, `postid` FROM `post` WHERE `pagetext` LIKE > '%someuniqueidentifier%'ORDER BY `postid` DESC > > This query works fine for what I needed. Now, the requirement has > changed to finding that latest object posted in a specific forum but > the forum table has no direct reference to the postid. The thread > table has a reference to the forumID but not a postID. > > It looked something like this: > posts table: > > +---------+------------+ > | post_id | thread_id | > +--------+-------------+ > > forum table: > > +----------+------------+ > | forum_id | | > +----------+------------+ > > thread table: > > +------------+----------+ > | thread_id | forum_id | > +------------+----------+ > > I know the forum ID that contains the objects but need to query the > threads to see which ones contain posts with objects. Then grab the > last one for processing. > > So I think I have at least two queries now instead of the one I used to grab > that latest objects from the database. > > If I did manual queries, I would select the latest thread_id posted to > forum_id and then a second query to find all the posts in that thread > that contained objects and grab the last one posted. > > I think this all might be combined with a join but I am not clear > about how to do that because the conditional seems to need a result > that I don't have until I run the first query, i.e. WHERE > `forum_id`='163' AND `thread.thread_id`= (results of query to find > last thread) AND `pagetext` LIKE %someobjectidentifier% DESC LIMIT 1 > > So, I am still wondering if using JOIN is the right path to purse to > optimize this query. > > Any suggestion greatly appreciated. > > > -- > Best regards, > mikesz mailto:mik...@qualityadvantages.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > _________________________________________________________________ Send e-mail anywhere. No map, no compass. http://windowslive.com/oneline/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_anywhere_122008
