Jump to content

Archived

This topic is now archived and is closed to further replies.

aliendisaster

Slow SQL statement

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)'

Share this post


Link to post
Share on other sites
How are you searching a blob? That's supposed to hold binary data.

Share this post


Link to post
Share on other sites
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?

Share this post


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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.