From: Martin Gainty (mgaintyhotmail.com)
Date: Thu Jan 01 2009 - 08:46:45 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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: mikeszqualityadvantages.com
> To: mysqllists.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:mikeszqualityadvantages.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgaintyhotmail.com
>

_________________________________________________________________
Send e-mail anywhere. No map, no compass.
http://windowslive.com/oneline/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_anywhere_122008

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]