Slow SQL statement

[aliendisaster]

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?

[shoz]

If the userid should not be null then an INNER JOIN on user should be better than the "LEFT JOIN and a WHERE IS NOT NULL". Assuming userid is a column in table "user".

If you're using the LIKE clauses to do a search using "%string%", then try putting a [url=http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html]FULLTEXT INDEX[/url] on (history_subject, history_message, activity_subject, activity_message) and do a FULLTEXT search on it.

[quote=aliendisaster]
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?
[/quote]
The activity table has all the "message" information?

To make things clear, explain what the query should be doing.

Post the create statements for the tables
[code]
SHOW CREATE TABLE tablename
[/code]

Post the EXPLAIN output for the query
[code]
EXPLAIN SELECT DISTINCT userid ...
[/code]

[aliendisaster]

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

[fenway]

Well, first, you need the FULLTEXT index to span all relevant columns.  Second, distinct is not necessary, since you're getting back a UID anyway.

[aliendisaster]

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? 

[fenway]

You need just ONE fulltext index that spans all text columns that you want to be searched in any given table, NOT a fulltext index of each of these columns.

[aliendisaster]

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)'

[fenway]

How are you searching a blob? That's supposed to hold binary data.

[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?

[fenway]

It shouldn't be blob if it's text -- you'd have to check the refman for the conversion process, though.