aliendisaster

I'm just using [code]history_message LIKE '%string%'[/code] It will find the stuff but takes forever.  After I added the FULLTEXT index to the other columns and removed this section from the WHERE everything worked perfect so I believe this is whats causing the slowness.  Would it be wise to change it to text?  I did not setup the database so I'm unsure why its set as blob and I see no reason for it to be.  If I make the change to text, would it convert the information stored to text or remove it?

Ok.  I figured it with respect to the FULLTEXT index.  Only problem is I'm searching by history_message which is a blob and I can not set a FULLTEXT index on this.  Any sugesstions on what I can do about this? Here is the create table for the history table: CREATE TABLE `history` (\n  `history_id` int(12) NOT NULL auto_increment,\n  `history_date` timestamp NOT NULL default CURRENT_TIMESTAMP,\n  `history_created_by` int(9) NOT NULL default '0',\n  `history_created_for` int(9) NOT NULL default '0',\n  `history_created_by_type` enum('agent','employee') NOT NULL default 'agent',\n  `history_created_for_type` enum('agent','customer') NOT NULL default 'agent',\n  `history_type` enum('call','email','note','referral in','referral out') NOT NULL default 'call',\n  `history_direction` enum('in','out','n/a') NOT NULL default 'n/a',\n  `history_durration` int(9) unsigned default NULL,\n  `history_subject` varchar(45) default NULL,\n  `history_message` blob,\n  `history_flag` enum('none','refferal') default NULL,\n  PRIMARY KEY  (`history_id`),\n  KEY `history_lookup` (`history_created_by`,`history_created_for`,`history_created_for_type`,`history_type`,`history_direction`,`history_date`,`history_created_by_type`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='history information (calls, emails, notes)'

So I would need to put FULLTEXT index on all columns (user_id, person_name_first, etc)?  I put the FULLTEXT index on activity_subject and activity_message.  I thought this would be all I needed.  I have other querys that use where user_id = '4' (for example), will this cause problems with those? 

The query is a search query to search through out history and activities for a keyword.  Both history and activities have a subject and a message.  I tried just focusing on the activities table and made the subject and message FULLTEXT.  Here is the new SQL statement: [code] SELECT DISTINCT user_id,   person_name_first f_name,   person_name_last l_name,   person_email_primary email,   person_address_a address,   person_address_a2 address_2,   person_address_a_city city,   person_address_a_zip zip,   person_address_a_state state,   user__user_group.usr_usr_grp_group_id group_id,   user.day_of_acc date_added,   UNIX_TIMESTAMP(user.user_last_login ) date_viewed FROM person INNER JOIN user ON user_person_id = person_id INNER JOIN user__user_group ON usr_usr_grp_user_id = user_id INNER JOIN history ON history_created_by = user_id INNER JOIN activity ON activity_owner_id = user_id WHERE user_id IS NOT NULL AND (MATCH (activity_subject, activity_message) AGAINST ('%string%')) [/code] I recieve the following error: Can't find FULLTEXT index matching the column list Here is the create table statement for activity: CREATE TABLE `activity` (\n  `activity_id` int(13) unsigned NOT NULL auto_increment,\n  `activity_type` enum('appt','task') NOT NULL default 'task',\n  `activity_expires_on` enum('exp date','completion','indefinite') NOT NULL default 'exp date',\n  `activity_created_by` int(7) unsigned NOT NULL default '0',\n  `activity_owner_type` enum('user','group') NOT NULL default 'user',\n  `activity_owner_id` int(7) unsigned NOT NULL default '0',\n  `activity_with_type` enum('owner','agent','customer') NOT NULL default 'customer',\n  `activity_with_id` int(9) unsigned default NULL,\n  `activity_status` int(1) unsigned NOT NULL default '0',\n  `activity_availablity` enum('available','unavailable') NOT NULL default 'available',\n  `activity_date_assigned` timestamp NULL default NULL,\n  `activity_date_scheduled` timestamp NULL default NULL,\n  `activity_date_completed` timestamp NULL default NULL,\n  `activity_date_expires` timestamp NULL default NULL,\n  `activity_length` int(7) NOT NULL default '30',\n  `activity_subject` varchar(45) default NULL,\n  `activity_message` text,\n  `activity_reminder_type` enum('none','txt-msg','email') NOT NULL default 'none',\n  `activity_reminder_offset` int(5) unsigned default NULL,\n  `activity_reminder_date_send` timestamp NULL default NULL,\n  `activity_reminder_date_sent` timestamp NULL default NULL,\n  `activity_reminder_send_to` varchar(50) default NULL,\n  `recur` varchar(250) NOT NULL default 'monday',\n  `prior` varchar(2) NOT NULL default 'b',\n  PRIMARY KEY  (`activity_id`),\n  KEY `activity_lookup` (`activity_owner_id`,`activity_date_scheduled`,`activity_id`,`activity_owner_type`),\n  KEY `activity_date_expires` (`activity_date_expires`),\n  KEY `activity_with_type` (`activity_with_type`,`activity_with_id`),\n  KEY `activity_status` (`activity_status`),\n  KEY `activity_unavailable` (`activity_availablity`),\n  FULLTEXT KEY `activity_message` (`activity_message`),\n  FULLTEXT KEY `activity_subject` (`activity_subject`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='activity information' Also here is the output from the explain of the original SQL statement: id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra 1 SIMPLE activity ALL activity_lookup,activity_message,activity_subject NULL NULL NULL 623 Using where; Using temporary 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 f_agent.activity.activity_owner_id 1 Using where 1 SIMPLE user__user_group ref usr_usr_grp_user_id usr_usr_grp_user_id 4 f_agent.user.user_id 1 Using index 1 SIMPLE person eq_ref PRIMARY PRIMARY 4 f_agent.user.user_person_id 1 Using where 1 SIMPLE history ref history_lookup history_lookup 4 f_agent.user.user_id 307 Using index; Distinct

What collation are you using on this field?  This could be causing the  to display. You can try changing the collation on this field and see if that removes the character. Another way to remove it would be to use str_replace to remove it like so: [code]echo str_replace('Â', '', $array[1])[/code]

I have the following SQL statement: [code]SELECT DISTINCT user_id,   person_name_first f_name,   person_name_last l_name,   person_email_primary email,   person_address_a address,   person_address_a2 address_2,   person_address_a_city city,   person_address_a_zip zip,   person_address_a_state state,   user__user_group.usr_usr_grp_group_id group_id,   user.day_of_acc date_added,   UNIX_TIMESTAMP(user.user_last_login ) date_viewed FROM person LEFT JOIN user ON user_person_id = person_id LEFT JOIN user__user_group ON usr_usr_grp_user_id = user_id LEFT JOIN history ON history_created_by = user_id LEFT JOIN activity ON activity_owner_id = user_id WHERE user_id IS NOT NULL AND (history_subject LIKE {str} OR history_message LIKE {str} OR activity_subject LIKE {str} OR activity_message LIKE {str})[/code] It pulls out the correct information but takes forever.  If I remove the activity LEFT JOIN and WHERE statements, everything works perfect (except I dont have the activity stuff).  How can I optimize this statement so it will pull out the activity information and not take 10 minutes?