Re: Creating an INDEX on multiple tables?

I have created FULLTEXT indexes on each of the tables that needs to be searched. I have had to break each of the tables up into a maximum of 16 fields or less for the FULLTEXT index to be created (I received a mySQL error to tell me this). The reason I am sending this message is I wonder if you would help me with the WHERE MATCH ( ) This is the first time I have done something like this. Specifically I am wondering if the FULLTEXT index names may be used (search_contact, search_details, search_activity, search_categories) or do I need to specify each of the fields from each table? Here is the beginning of the mySQL query with all the INNER JOIN's. SELECT ministry_profiles.reference, ministry_profiles.organization FROM ( ( ( ministry_profiles INNER JOIN ministry_profiles_listing_details ON ministry_profiles.reference = ministry_profiles_listing_details.ministry_profile_reference ) INNER JOIN ministry_profiles_activity ON ministry_profiles.reference = ministry_profiles_activity.ministry_profiles_reference ) INNER JOIN ministry_profile_categories ON = ministry_profiles.reference = ministry_profile_categories.ministry_profiles_reference ) INNER JOIN ministry_categories ON ministry_profile_categories.ministry_categories_reference = ministry_categories.reference WHERE MATCH( ) AGAINST ('$search') AND ministry_profiles.live =1 AND ministry_profiles_activity.live =1 The fields I need to be searched are in the FULLINDEX which is part of each table description. Here are the tables: Table structure for table ministry_profiles NOTE: This is the base table. The common field between this table and the others is ministry_profiles.reference -- CREATE TABLE IF NOT EXISTS ministry_profiles ( reference int(10) NOT NULL AUTO_INCREMENT, organization varchar(250) NOT NULL, address_line_1 varchar(100) NOT NULL, address_line_2 varchar(100) NOT NULL, city varchar(100) NOT NULL, province_state varchar(100) NOT NULL, postal_zip_code varchar(25) NOT NULL, country varchar(2) NOT NULL, telephone varchar(50) NOT NULL, toll_free varchar(50) NOT NULL, fax varchar(20) NOT NULL, email varchar(250) NOT NULL, website varchar(250) NOT NULL, live int(1) NOT NULL DEFAULT '0', PRIMARY KEY (reference), FULLTEXT KEY search_contact (organization,address_line_1,address_line_2,city,province_state,postal_zip_code,country,telephone,toll_free,fax,email,website) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1199 ; Table structure for table ministry_profiles_listing_details -- CREATE TABLE IF NOT EXISTS ministry_profiles_listing_details ( reference int(10) NOT NULL AUTO_INCREMENT, ministry_profile_reference int(10) NOT NULL, contact varchar(100) NOT NULL, year_founded varchar(10) NOT NULL, volunteer_opportunities varchar(1000) NOT NULL, employment_opportunities varchar(1000) NOT NULL, members_of varchar(500) NOT NULL, major_events varchar(1000) NOT NULL, assoiciate_member_of: varchar(500) NOT NULL, registration_number varchar(100) NOT NULL, fundraising varchar(100) NOT NULL, PRIMARY KEY (reference), FULLTEXT KEY search_details (contact,year_founded,volunteer_opportunities,employment_opportunities,members_of,major_events,assoiciate_member_of:,registration_number,fundraising) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Table structure for table ministry_profiles_activity -- CREATE TABLE IF NOT EXISTS ministry_profiles_activity ( reference int(20) NOT NULL AUTO_INCREMENT, ministry_profiles_reference int(10) NOT NULL, activity varchar(1500) NOT NULL, live int(1) NOT NULL DEFAULT '0', PRIMARY KEY (reference), FULLTEXT KEY search_activity (activity) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Now the listing categories. Categories are in their own table (ministry_categories) and then links created with INNER JOINS in a second table (ministry_profile_categories) Table structure for table ministry_categories -- CREATE TABLE IF NOT EXISTS ministry_categories ( reference int(4) NOT NULL AUTO_INCREMENT, category varchar(100) NOT NULL, PRIMARY KEY (reference), UNIQUE KEY category (category), FULLTEXT KEY search_categories (category) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=320 ; Table structure for table ministry_profile_categories -- CREATE TABLE IF NOT EXISTS ministry_profile_categories ( reference int(100) NOT NULL AUTO_INCREMENT, ministry_profiles_reference int(10) NOT NULL, ministry_categories_reference int(4) NOT NULL, PRIMARY KEY (reference), UNIQUE KEY ministry_profiles_activity_reference (ministry_profiles_reference,ministry_categories_reference) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1568 ; Ron -------------------------------------------------- From: "Bastien" <phpster@gmail.com> Sent: Thursday, October 21, 2010 7:12 AM To: "Artur Ejsmont" <ejsmont.artur@gmail.com> Cc: "Ron Piggott" <ron.piggott@actsministries.org>; <php-db@lists.php.net> Subject: Re: [PHP-DB] Creating an INDEX on multiple tables?

Alternatively, you could try setting that index on each table and use a UNION to join multiple queries together provided each query returns the identical data set structures ( or the column types have to match) Bastien Koert 905-904-0334 Sent from my iPhone On 2010-10-21, at 7:06 AM, Artur Ejsmont <ejsmont.artur@gmail.com> wrote:

I dont think you can create such index across tables. If you are interested read up on sphinx. Im pretty sure you would be able to create what you need. Alternatively ... a super simplistic solution ..... create one extra search table with copy of the data and create index there? ;P hehehe + would let you do what you need - would require a lot more IO to support the extra writes (to keep copy in sync) It would be cool if a fulltext index could be created on a view :) Art On 21 October 2010 09:43, Ron Piggott <ron.piggott@actsministries.org> wrote:

Is it possible to create one index on multiple tables? I am trying to create a search function for my web site. The data the user needs to be able to search is stored in multiple tables. I would like to be able to use "MATCH / AGAINST", like the query below I found online. SELECT firstname, lastname,comments FROM users WHERE MATCH(firstname,lastname,comments) AGAINST ('$searchterm') Ron

-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php