HowdeeDoodee Posted June 29, 2007 Share Posted June 29, 2007 This is a 2 part question. Part 1 In database1, I have three longtext fields that are not indexed. This database is used for single word searches and exact phrase searches including searches containing the words "or" or "and". If I use database 1, I need to index these three longtext fields. What is the best way, i.e., how long an index string is best for longtext fields? Part 2 In database2, the longtext fields and a shorter text field are indexed full text. The script calling database2 automatically detects the words "or" or "and" in a search phrase and activates a full text query when these words are found in the search term. Another consideration is the elimation of stop words or short words in the full text search scheme. Short words in a phrase would be eliminated in an exact phrase search. Can I use database2 for non-fulltext queries and still be able to search for exact phrase searches containing the words "or" or "and" like I am doing in database1? If so, how do I search for exact phrases using a fulltext index? Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/ Share on other sites More sharing options...
fenway Posted June 29, 2007 Share Posted June 29, 2007 You can't index a text field in the usual text -- fulltext is your only option. you'll have to use 'like %and%' for what you describe, unless you edit the stopwords file and length and recomplie the server. Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-285828 Share on other sites More sharing options...
HowdeeDoodee Posted June 29, 2007 Author Share Posted June 29, 2007 The reason I am posing the question is because of access time on the server. For database1 I just ran an exact phrase search using the AND operator and the return was... Showing rows 1 (1 total, Query took 10.0501 sec) Then I ran the same query again using the OR operator I use in my script and the return was... Showing rows 0 - 23 (24 total, Query took 0.0501 sec) The query searched one field that is indexed, text, and two fields that are not indexed at all. So, do I have an issue? Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-285839 Share on other sites More sharing options...
fenway Posted June 29, 2007 Share Posted June 29, 2007 Post both queries and the explain output. Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-285865 Share on other sites More sharing options...
HowdeeDoodee Posted June 29, 2007 Author Share Posted June 29, 2007 SELECT * FROM `View2_Cond` WHERE `Topic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci AND `Subtopic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci AND `Theswords` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci Showing rows 1 (1 total, Query took 10.0501 sec) SELECT * FROM `View2_Cond` WHERE `Topic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci OR `Subtopic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci OR `Theswords` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci Showing rows 0 - 23 (24 total, Query took 0.0501 sec) SELECT * FROM `View2_Cond` WHERE `Topic` LIKE CONVERT(_utf8 '%the game%' USING latin1) COLLATE latin1_general_ci OR `Subtopic` LIKE CONVERT(_utf8 '%the game%' USING latin1) COLLATE latin1_general_ci OR `Theswords` LIKE CONVERT(_utf8 '%the game%' USING latin1) COLLATE latin1_general_ci Showing rows 0 - 29 (47 total, Query took 0.0293 sec) Do I have an issue or do I leave well enough alone? Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-285913 Share on other sites More sharing options...
Illusion Posted June 29, 2007 Share Posted June 29, 2007 as you have fulltext index it is always better make use of MATCH() ..........AGAINST() and also you can do multiple column search with single query if u have same search string. Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-285922 Share on other sites More sharing options...
fenway Posted June 29, 2007 Share Posted June 29, 2007 SELECT * FROM `View2_Cond` WHERE `Topic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci AND `Subtopic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci AND `Theswords` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci Showing rows 1 (1 total, Query took 10.0501 sec) SELECT * FROM `View2_Cond` WHERE `Topic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci OR `Subtopic` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci OR `Theswords` LIKE CONVERT(_utf8 '%child of man%' USING latin1) COLLATE latin1_general_ci Showing rows 0 - 23 (24 total, Query took 0.0501 sec) SELECT * FROM `View2_Cond` WHERE `Topic` LIKE CONVERT(_utf8 '%the game%' USING latin1) COLLATE latin1_general_ci OR `Subtopic` LIKE CONVERT(_utf8 '%the game%' USING latin1) COLLATE latin1_general_ci OR `Theswords` LIKE CONVERT(_utf8 '%the game%' USING latin1) COLLATE latin1_general_ci Showing rows 0 - 29 (47 total, Query took 0.0293 sec) Do I have an issue or do I leave well enough alone? And the EXPLAIN output, too.... also run the fulltext one as illusion suggests. Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-286003 Share on other sites More sharing options...
HowdeeDoodee Posted June 29, 2007 Author Share Posted June 29, 2007 Thank you to both of you for the responses. Your help is appreciated very much. Explain output: The output is generated by using phpMyAdmin. All fields are displayed in phpMyAdmin as one would normally expect the fields to be displayed. Here is a comparison between a search in regular query search mode and boolean search mode. Regular query search mode. SELECT * FROM `View2_Concord` WHERE (`Source` = 'NV' OR `Source` = 'TR' OR `Source` = 'BT') AND (Topic LIKE '%power of truth%' OR Subtopic LIKE '%power of truth%' OR Theswords LIKE '%power of truth%') ORDER BY `Lnum` ASC Showing rows 0 - 23 (24 total, Query took 0.0630 sec) Acccurately finding exact phrase by searching three fields where only one field is indexed. Boolean search mode SELECT * FROM `View2_ConcordFT` WHERE MATCH(Topic, Subtopic, Theswords) AGAINST ('power of truth' IN BOOLEAN MODE) ORDER BY `Lnum` ASC Showing rows 0 - 29 (339 total, Query took 0.0117 sec) Not accurately finding exact phrase because the word "of" is automatically deleted and only "power" and "truth" are searched for. Do you think the speed of the regular search mode is of any concern? My host provider says 10 seconds is too much. How am I going to get an exact match of phrases by using boolean? Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-286093 Share on other sites More sharing options...
fenway Posted June 29, 2007 Share Posted June 29, 2007 *sigh* I don't mean "explain the output", I mean run EXPLAIN on your query -- add the keyword "EXPLAIN" in front of SELECT (see the link in my sig). Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-286096 Share on other sites More sharing options...
HowdeeDoodee Posted June 29, 2007 Author Share Posted June 29, 2007 I will now take a break to laugh at myself as I try to explain... :D OK, here we go Host: localhost Database: find_DBProj Generation Time: Jun 29, 2007 at 02:28 PM Generated by: phpMyAdmin 2.9.0.2 / MySQL 4.1.21-standard-log EXPLAIN SELECT * FROM `View2_Cond` WHERE (`Source` = 'NV' OR `Source` = 'TR' OR `Source` = 'BT') AND (Topic LIKE '%power of truth%' OR Subtopic LIKE '%power of truth%' OR Theswords LIKE '%power of truth%') ORDER BY `Lnum` ASC; Rows: 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE View2_Cond ALL Source NULL NULL NULL 10675 Using where; Using filesort Host: localhost Database: find_DBProj Generation Time: Jun 29, 2007 at 02:32 PM Generated by: phpMyAdmin 2.9.0.2 / MySQL 4.1.21-standard-log EXPLAIN SELECT * FROM `View2_CondFT` WHERE MATCH(Topic, Subtopic, Theswords) AGAINST ('power of truth' IN BOOLEAN MODE) ORDER BY `Lnum` ASC ; Rows: 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE View2_CondFT fulltext Topic Topic 0 1 Using where; Using filesort Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-286174 Share on other sites More sharing options...
fenway Posted July 1, 2007 Share Posted July 1, 2007 Well, look at the difference in rows being examined! Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-287036 Share on other sites More sharing options...
HowdeeDoodee Posted July 2, 2007 Author Share Posted July 2, 2007 Thank you. Quote Link to comment https://forums.phpfreaks.com/topic/57730-solved-indexing-question-and-using-a-full-text-db-vs-a-non-full-text-db/#findComment-287577 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.