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@xxxxxxxxx>
Sent: Thursday, October 21, 2010 7:12 AM
To: "Artur Ejsmont" <ejsmont.artur@xxxxxxxxx>
Cc: "Ron Piggott" <ron.piggott@xxxxxxxxxxxxxxxxxx>; <php-db@xxxxxxxxxxxxx>
Subject: Re: 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
