Jump to content

Slow SQL statement


aliendisaster

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.