List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:January 1 2009 2:46pm
Subject:RE: Can a JOIN statement do this?
View as plain text  
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: mikesz@stripped
> To: mysql@stripped
> 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:mikesz@stripped
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
Send e-mail anywhere. No map, no compass.
http://windowslive.com/oneline/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_anywhere_122008
Thread
Can a JOIN statement do this?mikesz1 Jan
  • RE: Can a JOIN statement do this?Martin Gainty1 Jan