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
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.