MySQL Forums :: PHP :: Complex search - multiple fields and tables - need advice.

Hey Everyone,

My first post here - so I apologize if I don't supply enough information to answer my question, but I will try. I am fairly new to mysql/php, so please keep that in mind when replying, but don't be afraid to offer critical advice!

I am attempting to build a basic contact management system and I require a keyword search across multiple fields of the primary table as well as fields of multiple other tables. I currently have this functioning, however it is brutally slow and I could use some advice on how to speed it up. Actually - I should clarify - I have built the system in such a manner that the keyword search and second 2 joins only occur when a keyword search is actually performed. If the user is browsing the list of contacts as normal it is perfectly fine.

The current table structures and SQL will be posted at the bottom of this message.

I have made an attempt to create the appropriate indexes, however my knowledge here is fairly limited so I expect that I have not created the most optimized indexes. From my research I can see that different table types (InnoDB or MyISAM) can also effect SELECTS and INSERTS of data and I feel that using MyISAM is best for my usage, but if anyone has an alternate opinion I would be very interested in hearing it.

Also - I understand that my overall table structure may not be ideal - I would also be open to advice on this as well.

Currently I am looking into two possible ways to speed up my searches: Using different JOINS (not exactly sure how each of them work, but am reading and running some experiments, so far I can see that my number of results varies between using different join types - but I have yet to figure out exactly what is different between the two (in terms of my data set)) and creating a "keyword" table which would hold the contact_id and a field with all of the associated keywords which I could update via a PHP function. This seems very tedious though.

Any and all advice is welcome and appreciated. If I need to supply more information please let me know. I don't believe I need to supply a bunch of INSERTS as this is more of a theoretical question rather than a specific error type of question.

Cheers!

Luke


Current SQL Statement:

SELECT `c`.`contact_id`, `c`.`first_name`, `c`.`last_name`, `c`.`email_address`, `c`.`city`, `c`.`rating`, `c`.`date_created`, `cej`.`assigned_dt`
FROM `contacts` `c` 
LEFT JOIN `contact_employee_join` `cej` ON `c`.`contact_id` = `cej`.`contact_id`
LEFT JOIN `employee_locations` `el` ON `cej`.`employee_id` = `el`.`employee_id`
LEFT JOIN `contact_notes` `cn` ON `c`.`contact_id` = `cn`.`contact_id` 
LEFT JOIN `contact_communications` `cc` ON `c`.`contact_id` = `cc`.`contact_id`
WHERE (`c`.`first_name` LIKE '%$term%' || 
   `c`.`last_name` LIKE '%$term%' || 
   `c`.`address1` LIKE '%$term%' || 
   `c`.`address2` LIKE '%$term%' || 
   `c`.`city` LIKE '%$term%' ||
   `cn`.`note` LIKE '%$term%' || 
   `cc`.`notes` LIKE '%$term%') &&
   (`c`.`rating` = '$rating') &&
   ($date_field BETWEEN '$date_from' AND '$date_to') &&
   (`cej`.`employee_id` = '$employee_id')
ORDER BY `c`.`last_name`, `c`.`first_name` ASC

Table Structures:

CREATE TABLE `contacts` (
  `contact_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(32) NOT NULL,
  `last_name` varchar(32) NOT NULL,
  `address1` varchar(32) NOT NULL,
  `address2` varchar(32) NOT NULL,
  `city` varchar(64) NOT NULL,
  `province` varchar(2) NOT NULL,
  `country` varchar(2) NOT NULL,
  `postal_code` varchar(12) NOT NULL,
  `home_phone` varchar(12) NOT NULL,
  `cell_phone` varchar(12) NOT NULL,
  `work_phone` varchar(12) NOT NULL,
  `work_phone_ext` varchar(6) NOT NULL,
  `fax` varchar(12) NOT NULL,
  `email_address` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `notes` text NOT NULL,
  `subscribed` enum('No','Yes') NOT NULL DEFAULT 'No',
  `contact_source` varchar(32) DEFAULT NULL,
  `rating` varchar(12) NOT NULL,
  `location_id` mediumint(9) NOT NULL,
  `date_created` date NOT NULL,
  PRIMARY KEY (`contact_id`),
  KEY `last_name` (`last_name`),
  KEY `first_name` (`first_name`),
  KEY `last_first` (`last_name`,`first_name`)
) ENGINE=MyISAM AUTO_INCREMENT=4321 DEFAULT CHARSET=utf8

CREATE TABLE `contact_communications` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `contact_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `type` varchar(32) NOT NULL,
  `direction` varchar(32) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`log_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5155 DEFAULT CHARSET=utf8

CREATE TABLE `contact_notes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `contact_id` int(11) DEFAULT NULL,
  `note` text,
  `added_by` varchar(64) DEFAULT NULL,
  `added_dt` datetime DEFAULT NULL,
  PRIMARY KEY (`note_id`)
) ENGINE=MyISAM AUTO_INCREMENT=995 DEFAULT CHARSET=utf8

CREATE TABLE `contact_custom_data` (
  `contact_id` int(11) NOT NULL DEFAULT '0',
  `custom_field_id` int(11) NOT NULL DEFAULT '0',
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`contact_id`,`custom_field_id`),
  KEY `contact_id` (`contact_id`),
  KEY `custom_field_id` (`custom_field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `contact_employee_join` (
  `contact_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `assigned_by` varchar(64) NOT NULL,
  `assigned_dt` datetime NOT NULL,
  PRIMARY KEY (`contact_id`,`employee_id`),
  KEY `contact_id` (`contact_id`),
  KEY `employee_id` (`employee_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `employee_locations` (
  `employee_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  PRIMARY KEY (`employee_id`,`location_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8