aliendisaster Posted September 25, 2006 Share Posted September 25, 2006 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_viewedFROM person LEFT JOIN user ON user_person_id = person_idLEFT JOIN user__user_group ON usr_usr_grp_user_id = user_idLEFT JOIN history ON history_created_by = user_idLEFT JOIN activity ON activity_owner_id = user_idWHERE 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? Quote Link to comment Share on other sites More sharing options...
shoz Posted September 25, 2006 Share Posted September 25, 2006 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] Quote Link to comment Share on other sites More sharing options...
aliendisaster Posted September 25, 2006 Author Share Posted September 25, 2006 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_viewedFROM person INNER JOIN user ON user_person_id = person_idINNER JOIN user__user_group ON usr_usr_grp_user_id = user_idINNER JOIN history ON history_created_by = user_idINNER JOIN activity ON activity_owner_id = user_idWHERE 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 listHere 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 Extra1 SIMPLE activity ALL activity_lookup,activity_message,activity_subject NULL NULL NULL 623 Using where; Using temporary1 SIMPLE user eq_ref PRIMARY PRIMARY 4 f_agent.activity.activity_owner_id 1 Using where1 SIMPLE user__user_group ref usr_usr_grp_user_id usr_usr_grp_user_id 4 f_agent.user.user_id 1 Using index1 SIMPLE person eq_ref PRIMARY PRIMARY 4 f_agent.user.user_person_id 1 Using where1 SIMPLE history ref history_lookup history_lookup 4 f_agent.user.user_id 307 Using index; Distinct Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2006 Share Posted September 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
aliendisaster Posted September 25, 2006 Author Share Posted September 25, 2006 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2006 Share Posted September 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
aliendisaster Posted September 25, 2006 Author Share Posted September 25, 2006 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)' Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2006 Share Posted September 25, 2006 How are you searching a blob? That's supposed to hold binary data. Quote Link to comment Share on other sites More sharing options...
aliendisaster Posted September 25, 2006 Author Share Posted September 25, 2006 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2006 Share Posted September 25, 2006 It shouldn't be blob if it's text -- you'd have to check the refman for the conversion process, though. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.