Jump to content


Photo

Slow SQL statement


  • Please log in to reply
9 replies to this topic

#1 aliendisaster

aliendisaster
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 September 2006 - 03:26 PM

I have the following SQL statement:

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

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?

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 September 2006 - 05:56 PM

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 FULLTEXT INDEX on (history_subject, history_message, activity_subject, activity_message) and do a FULLTEXT search on it.

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?

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
SHOW CREATE TABLE tablename

Post the EXPLAIN output for the query
EXPLAIN SELECT DISTINCT userid ...


#3 aliendisaster

aliendisaster
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 September 2006 - 06:29 PM

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:

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

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 06:32 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 aliendisaster

aliendisaster
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 September 2006 - 06:40 PM

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? 

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 06:54 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 aliendisaster

aliendisaster
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 September 2006 - 07:08 PM

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

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 08:47 PM

How are you searching a blob? That's supposed to hold binary data.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 aliendisaster

aliendisaster
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 September 2006 - 08:56 PM

I'm just using

history_message LIKE '%string%'

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?

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 09:38 PM

It shouldn't be blob if it's text -- you'd have to check the refman for the conversion process, though.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users