Re: Creating an INDEX on multiple tables?
| From: | Ron Piggott | Date: | Fri, 22 Oct 2010 04:24:16 +0000 |
| Subject: | Re: Creating an INDEX on multiple tables? | ||
| References: | 1 2 3 4 5 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-47414@lists.php.net to get a copy of this message | ||
The query works using UNION ALL and MATCH ( ) AGAINST --- one table at a time for the MATCH. The down side is that each SELECT is giving it's own search results. The directory listings are being displayed multiple times when one of the SELECTS produces it as a result. How do I limit the results? Can I do this some how with results.reference and results.organization (I made the UNION ALLs sub queries)
Thanks for the help.
Ron
SELECT reference, organization FROM (
SELECT
ministry_profiles.reference, ministry_profiles.organization FROM
(
(
( ministry_profiles LEFT OUTER JOIN ministry_profiles_listing_details ON ministry_profiles.reference = ministry_profiles_listing_details.ministry_profile_reference )
LEFT OUTER JOIN ministry_profiles_activity ON ministry_profiles.reference = ministry_profiles_activity.ministry_profiles_reference )
LEFT OUTER JOIN ministry_profile_categories ON ministry_profiles.reference = ministry_profile_categories.ministry_profiles_reference )
LEFT OUTER JOIN ministry_categories ON ministry_profile_categories.ministry_categories_reference = ministry_categories.reference
WHERE
MATCH( ministry_profiles.organization, ministry_profiles.address_line_1, ministry_profiles.address_line_2, ministry_profiles.city, ministry_profiles.province_state, ministry_profiles.postal_zip_code, ministry_profiles.country, ministry_profiles.telephone, ministry_profiles.toll_free, ministry_profiles.fax, ministry_profiles.email, ministry_profiles.website )
AGAINST
('$search')
AND
ministry_profiles.live =1
GROUP BY ministry_profiles.reference
UNION ALL
SELECT ministry_profiles.reference, ministry_profiles.organization FROM
(
(
( ministry_profiles LEFT OUTER JOIN ministry_profiles_listing_details ON ministry_profiles.reference = ministry_profiles_listing_details.ministry_profile_reference )
LEFT OUTER JOIN ministry_profiles_activity ON ministry_profiles.reference = ministry_profiles_activity.ministry_profiles_reference )
LEFT OUTER JOIN ministry_profile_categories ON ministry_profiles.reference = ministry_profile_categories.ministry_profiles_reference )
LEFT OUTER JOIN ministry_categories ON ministry_profile_categories.ministry_categories_reference = ministry_categories.reference
WHERE
MATCH( ministry_categories.category )
AGAINST
('$search')
GROUP BY ministry_profiles.reference
UNION ALL
SELECT ministry_profiles.reference, ministry_profiles.organization FROM
(
(
( ministry_profiles LEFT OUTER JOIN ministry_profiles_listing_details ON ministry_profiles.reference = ministry_profiles_listing_details.ministry_profile_reference )
LEFT OUTER JOIN ministry_profiles_activity ON ministry_profiles.reference = ministry_profiles_activity.ministry_profiles_reference )
LEFT OUTER JOIN ministry_profile_categories ON ministry_profiles.reference = ministry_profile_categories.ministry_profiles_reference )
LEFT OUTER JOIN ministry_categories ON ministry_profile_categories.ministry_categories_reference = ministry_categories.reference
WHERE
MATCH( ministry_profiles_activity.activity )
AGAINST
('$search')
AND ministry_profiles_activity.live =1
GROUP BY ministry_profiles.reference
UNION ALL
SELECT ministry_profiles.reference, ministry_profiles.organization FROM
(
(
( ministry_profiles LEFT OUTER JOIN ministry_profiles_listing_details ON ministry_profiles.reference = ministry_profiles_listing_details.ministry_profile_reference )
LEFT OUTER JOIN ministry_profiles_activity ON ministry_profiles.reference = ministry_profiles_activity.ministry_profiles_reference )
LEFT OUTER JOIN ministry_profile_categories ON ministry_profiles.reference = ministry_profile_categories.ministry_profiles_reference )
LEFT OUTER JOIN ministry_categories ON ministry_profile_categories.ministry_categories_reference = ministry_categories.reference
WHERE
MATCH( ministry_profiles_listing_details.contact, ministry_profiles_listing_details.year_founded, ministry_profiles_listing_details.volunteer_opportunities, ministry_profiles_listing_details.employment_opportunities, ministry_profiles_listing_details.members_of, ministry_profiles_listing_details.major_events, ministry_profiles_listing_details.associate_member_of, ministry_profiles_listing_details.registration_number, ministry_profiles_listing_details.fund_raising )
AGAINST
('$search')
GROUP BY ministry_profiles.reference
) AS results