Slow FullText Search When Table Grows Constantly
I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields that contain over 20 000 characters on each row.
The thing is, as I could observe, that when multiple different full-text searches are made in appropiate period of time, the query doesn't take so much to respond (though i couldn't quite say it's fast), but when no search is made, let's say, for over 2-3 hours, the same query takes up to 30-40 seconds to respond.
The query is simple: .....
View Complete Forum Thread with Replies
Related Forum Messages:
Searching Table Which Is Being Constantly Updated Locks Up.
I'm logging mail to a database, and using php to make queries. The mail is coming in constantly, so the table is being added to, in batches of up to 30 mails. I have around 600k records. The queries are properly indexed, i think; i have index on date and domain, the fields I query on. Mostly the queries run quite fast, but sometimes the queries take up to two minutes. The query is a union of two queries into two different tables. mysqladmin -proc says the query is in state "sending data" for the whole time. Should it be a problem querying on a live table this size? Any parameters I can tweak and/or monitor? What if I seperated the data in two tables, one for today being updated constantly, and the other for the historical data, and todays data being moved to historical at midnight. Then queries to the historical data can be stored in the query cache.
View Replies !
FULLTEXT Search After Table Join
Its getting late so I hope I am coherent. I am building a database which displays discussion threads and replies in a nested folder-like structure. I have a threads table and a replies table linked by the thread id. In order to implement paging in the browser, Im using LIMIT on the threads table to display 25 threads at a time and then joining on the posts table. However, now I've added FULLTEXT searching on the replies. So my problem is my LIMIT clause is happening before the FULLTEXT search, so instead of getting 25 threads back, I get the subset which matched the FULLTEXT search. I know I can solve this by querying SELECT DISTINCT thread.id FROM threads JOIN replies ON thread.id = replies.thread_id WHERE MATCH(subject, msg) AGAINST ('searchstring') LIMIT offset, 25 and then perform another join to get these threads and their posts. Am I approaching this the correct way? Is there a more efficient that I'm not seeing?
View Replies !
Multiple Table Search Using Match (fulltext)
I am running a search on multiple tables for a match using: ... MATCH(table1.full_text_index) AGAINST ('string') OR MATCH (table2.full_text_index) AGAINST ('string') OR.... I want to be able to tell which table find a match. If there are multiple tables that find a match to able to identify which table found a match.
View Replies !
FullText Search :: #1191 - Can't Find FULLTEXT Index Matching The Column List
I have a table that was created and populated first and I have altered the table later to include the full text index.I get an error when I do a full text search like this: select name from table where match(name, address1, address2) against('text'); The error I receive is: #1191 - Can't find FULLTEXT index matching the column list But when I do the search with the in boolean mode it works just fine.Is that right or am I doing something wrong?? select name from establishments where match(name, address1, address2) against('restaurant' in boolean mode);
View Replies !
Fulltext Search Search Blob?
Does the fulltext search is a good way to find words in a table which contains more than 500'000 rows, moreover it will search in blob field. What's your opinion about fulltext in big table ?
View Replies !
Extreamly Slow Search?
I have the following 2 tables table pictureinarticle{ 'id' int(10) unsigned NOT NULL auto_increment, 'articleid' int(10) unsigned NOT NULL, 'pictureid' int(10) unsigned NOT NULL, PRIMARY KEY ('id'), KEY 'pictureid' ('picutreid', 'articleid') } table article{ 'id' int(10) unsigned NOT NULL auto_increment, 'timestamp' timestamp(14) NOT NULL, PRIMARY KEY ('id'), KEY 'timestamp' ('timestamp') } 'articleid' in table 'pictureinarticle' is PRIMARY KEY 'id' in 'article'. Now, I want to search total number of pictures in all articles that are in a time range START and END (both of TIMESTAMP type). It is a simple search as follows: select count(pictureid) from pictureinarticle where articleid in ( select id from article where timestamp between START and END); PROBLEM: - the above search took about 110 seconds to finish. Data: - Number of articles in [START, END] is around 260,000, returned from 'select id from article where timestamp between START and END'. - Number of records in table 'pictureinarticle' is around 1,400,000. Analysis: - select articles in [START, END] is fast, no more than 1 second: select id from article where timestamp between START and END. -it is 'select count(a.pictureid) from pictureinarticle a, article b where a.articleid in (...)' that actually cost 110 seconds. This seems to be due to a poor indexed table? But I did have 'articleid' indexed in table 'pictureinarticle'. So, what may cause this slow search problem? Will it help if we index on 'articleid' before 'pictureid' in table 'pictureinarticle'?
View Replies !
Fulltext Search Using LIKE
There is a request SELECT *, (MATCH body AGAINST ('john')) as relev FROM search HAVING relev>0 ORDER BY relev DESC. And everything is good, but it didn't find not "johnson" not "john's". Is it possible to solve it, may be by using LIKE?
View Replies !
MATCH Fulltext Search
I am doing a search now where I have a statement "FirstName LIKE '%$firstname%' OR JobTitle LIKE '%$jobtitle%' how can I do the same statement using full text search, for example with fulltext I would say MATCH (FirstName) AGAINST ('$firstname') OR MATCH (JobTitle) AGAINST ('$jobtitle) But that doesnt work, what is the correct way to do this query?
View Replies !
Fulltext Boolean Search
I need to be able to to do a Fulltext Boolean Search with excluded words only in mysql 4.1 Here's the manual on fulltext searches: fulltext mysql manual However the mysql manual states that negated searches like the one below will always return a NULL result: AND MATCH (resume_text) AGAINST ('-test' IN BOOLEAN MODE) So how can I return a result set on negated words only?
View Replies !
FullText Search :: Relevancy Value
We would like to begin supporting the truncation operator on our website's search engine--however, we still require the results to be sorted by relevance. If a client enters "televis*" into the search engine, and we perform the search in boolean mode, pages containing "television", "televise", "televised", etc will be returned. However, because it is a boolean mode search, all rows will have a relevancy value of 1.
View Replies !
Fulltext Search For 3 Words
my query would have to look like to only pull records that contained the specified words. Something like: SELECT * FROM table WHERE MATCH column AGAINST('word1 AND word2 AND word3'). I only want to see records that have all 3 words in them.
View Replies !
Fulltext Search Queries
My website has guitar lessons. If I do this query SELECT distinct lessonid FROM lessons WHERE match (text1,text2,text3) against ('scale')>0 I get no results. Scale appears in a large number of texts so I suspect it is returning nothing because there are too many instances. How do I control this?? I can see searching on'the' shouldn't return anything but my use of the word scale is not so frequent that it should be ignored.
View Replies !
Optmize Fulltext Search
I try to optimize my search results and I can't make it work properly, everytime I use IN BOOLEAN MODE the relevance or score are 1, if I remove IN BOOLEAN MODE I get the perfect search results and a real score like this 13.23435453 PHP Code: SELECT *, MATCH(name, introduction,keywords) AGAINST('$keyword' IN BOOLEAN MODE) AS relevance FROM sb_comp WHERE MATCH(name, introduction,keywords) AGAINST('$keyword' IN BOOLEAN MODE) HAVING relevance > 0.2 ORDER BY relevance DESC
View Replies !
Performing Fulltext Search
i'm strugling with my own small fulltext se for my site, and i'm still quite noobee with this... here's problem: i have entry in "products list" for example "HS-860", how can i set my sql to find that product. select * from products where match(product) against ("HS-860") don't bring result. i guess it considers this string as 2 keywords "HS and "860" - so length of both of them is below 4 chars and that's why it dont finds it.is there way i could write sql to find "HS-860" as one string?
View Replies !
FULLTEXT Search, With Filters?
I'm looking for a way to do a fulltext search on column "b". I know to do this I should do something along the lines of (I don't remember if there is a WHERE): select whatever from tablename match(b) against ("string" in boolean mode); Is there a way to search the following table with a fulltext search in "b" looking for the string "pina colada," while saying I only want results where a="n" .---.---.-------------------. |id | a | b | |---|---|-------------------| | 1 | y | pepperoni pizza | | 2 | y | hot dogs | | 3 | y | hamburger | | 4 | n | vodka | | 5 | n | pina colada (a) | | 6 | y | pina colada (n-a) | '---'---'-------------------' I guess you could say I'm trying to pseudo-do something like: do a fulltext search on "b" looking for the value "pina colada" where A="n"; Is there a way to do this, if so, could someone please tell me the syntax for it?
View Replies !
FULLTEXT Search For Numbers
I am having a problem searching the FULLTEXT index for matches to one of the coloumns in my DB that stores numbers even though the Datatype specified on the coloumn is VARCHAR. I am using the MATCH query to search three coloumns all of which have VARCHAR as their datatype. It works fine when I search by words but when I type in numbers it does not match anything. The value is passed to the query from the $_GET variable in PHP.
View Replies !
FullText Search And SQL Injections
I recently implemented fulltext indexing on a table that I use for searching and I was wondering how people avoid SQL injections when building their MATCH/AGAINST queries? Normally I would use a stored procedure and parameters to avoid SQL injections, but in this case there are an arbitrary number of words, and sometimes the search phrases might contain special characters (like + or '). For example, my search text might be "don't like animals" and that would be converted into "+don't* +like* +animals*". As you can see that query won't work because of the ' and this introduces to potential for SQL injections. Am I going about this the right way?
View Replies !
Fulltext Search Error
I'm trying to use full text searches with a query as such : SELECT content.id, content.user, image.ptitle FROM content LEFT JOIN image ON (content.image = image.id) WHERE MATCH(title,ptitle) AGAINST ('test') When I do this I get the following error : #1210 - Wrong arguments to MATCH If I do the query separately everything works. How do you go about having two columns in the match.
View Replies !
Fulltext Search Stopwords
I am using a shared server, I believe it has mysql 5.0 on it, but I think that they must have the stopwords for fulltext searches set to the default. I am having a problem with these restrictions because I have column that contains things like "looking left" and "looking right". The former works ok, and brings up results, while the later is stopped even though there are columns that contain both. I believe this is because the word "right" is stopped. Perhaps there is no solution to this other than changing the word "right" to "roight" both when the data is entered and searched for in the background using the scripting language, is there a solution in mySql, if there is no access or ability to change the stopwords
View Replies !
FULLTEXT Search 3 Letters
The default settings for fulltext search is to index words with more than 3 charaters long. Is there away to make it index "BMW" without changing the default settings to index all 3 character words? I have a database of cars, I don't want to index every 3 letter word, just BMW would be fine. Any idea if this can be done?
View Replies !
Fulltext Search Not Working
I followed the reference manual and my search function just doesn't work. Rather than explain it, here's my query: SELECT * FROM posts WHERE MATCH (title,post) AGAINST ('{$search}') ORDER BY `time` DESC ...
View Replies !
FULLTEXT Search Problem
I use MySQL 3.23.58 on Fedora Core 2. I have two varchar fields in a table, which I have indexed properly and observe the following problems with FULLTEXT: If the query finds one result it works ok. If the query finds more than one it returns none. For example I have 3 entries named 'hello', 'world' and 'world' respectively. FULLTEXT search for 'hello' returns entry 1, search for 'world' returns nothing! In addition, string matching works only for the exact string, ie search for 'hello' works, search for 'hell' does not work.
View Replies !
Mysql Fulltext Search
I have a document library database where I'm searching both the docTitle and docContent fields. Here is the basic query I'm passing through PHP: $query = "select *, match(docTitle, docContent) against('".$basicSearch."' IN BOOLEAN MODE) as relevance from content where match(docTitle, docContent) against('".$basicSearch."' IN BOOLEAN MODE) order by relevance DESC"; This is a good start for me. However there are one or two documents towards the bottom of the list ranked as a relevance of 1 or 2 where I would like them to be displayed at the top of the results. What I'm looking for is a way to have these searches weighted to where a document that has any of the words searched for in the docTitle weighted and ranked higher in the results than those documents that have the same words, but are not in the docTitle. Something else is, there are a few "main" documents in the database, labeled as "MD - (title)" in the docTitle. I would like my MD documents to ALWAYS be at the VERY TOP of the search results page, but then have the rest of the results displayed based on their regular relevance without the user having to type "MD" in their search query. Are either of these capable by just changing the way I'm querying the database... or is this something I need to do by post processing the array that is returned in PHP?
View Replies !
FULLTEXT Search Giving Me Nothing?
I'm new to MySQL FULLTEXT searching, so I'm hopping this'll be an easy fix. $query = "SELECT * FROM `downloads` WHERE MATCH (`title`,`desc`) AGAINST ('$name') AND level = 1 ORDER BY $orderby"; $query = mysql_query($query); The above query will find nothing when $name = 'ipod', even though I know 'ipod' is listed MANY times throughout the table. I've tried adding "IN BOOLEAN MODE" after '$name', but that gives me an error when trying to run mysql_fetch_array... What gives? On a side note, $query outputs as it should: SELECT * FROM `downloads` WHERE MATCH (`title`,`desc`) AGAINST ('ipod') AND level = 1 ORDER BY os ASC Edit: I found that if I run the following, ALL database rows are returned no matter what the search term: $querytxt = "SELECT *, MATCH (`title`,`desc`) AGAINST ('$name') FROM `downloads` WHERE level = 1 ORDER BY $orderby";
View Replies !
Odd Behavior With FULLTEXT Search
SELECT s01_Products.code , s01_Products.name , MATCH (s01_Products.name) AGAINST ('boxer briefs') AS score FROM s01_Products WHERE MATCH (s01_Products.name) AGAINST ('boxer briefs') && s01_Products.active = 1 my first couple results (CODE || NAME == SCORE): Quote: 145 Products: TX-3139 || 2(x)ist 3 Pack Fly Front Briefs == 5.2904501216034 CK-U5609 || Calvin Klein 365 Briefs - 2 Pack == 5.2322854282894 TU-RL68 || Polo Ralph Lauren Three Pack Mid Rise Briefs == 5.1197107702682 TU-RL69 || Polo Ralph Lauren Three Pack Low Rise Briefs == 5.1197107702682 GO-1003 || Boxer Brief == 1.919855709556 TX-3107 || Button Fly Boxer == 1.898273747088 Now if I alter my query ever so slightly (remove the s from briefs): SELECT s01_Products.code , s01_Products.name , MATCH (s01_Products.name) AGAINST ('boxer brief') AS score FROM s01_Products WHERE MATCH (s01_Products.name) AGAINST ('boxer brief') && s01_Products.active = 1 My results change, and I no longer get the first four: Quote: 141 Products: GO-1003 || Boxer Brief == 1.919855709556 TX-3107 || Button Fly Boxer == 1.898273747088 Now in my mind, the first four results on the "boxer briefs" query are irrelevant. No where in the name does the word "boxer" exist. The rest of the results are very accurate, and I especially like that towards the end some products with out Boxer match (with a score of 1.797255008972 or more). I just don't understand how the first four results have a score of 5 or more.
View Replies !
Fulltext Search Question
Today I built a fairly simple search engine for one of my clients' web sites. Essentially we've been trying to organize the search listings so that you can do a search for a specific brand of hotel. So for example, when we do a search for "hilton", we scan the "hotel_names" column of the "hotel" table in the database and up pops a list of all the Hilton hotels. But we have a problem. There's a brand of hotels named "W" hotels. When we do a search for "W" hotels, we get every single hotel in the database that has a "W" in the title. I've cut out some of the garbage by switching to a search for "W " (w, space)... but we still get a lot of hotels that just have words ending in "W". Each "W" brand hotel we have in the database starts like "W Chicago", or "W Hotel New York" or something like that... so there's characters before the W, and always a space immediately afterwards. Is there any way to do a fulltext search that checks against a regular expression? If no, any other ideas?
View Replies !
Fulltext Search For URLs
Below code is from a CMS's search script. When you search for plain words like Britney Spears it displays the results correctly. But when you search for a URL like http://www.sitepoint.com then it does not return any results. Is there anything wrong with the following SQL code? ....
View Replies !
FULLTEXT Search On 2 Tables
I am trying to do a FULLTEXT search on 2 tables containing products. The tables are default_products and custom_products. The custom_products is used to store customized data about the products and may have null values. These tables are linked by their product_id's. Initially I tried to do a FULLTEXT search using COALESCE which looked like this:
View Replies !
Fulltext Search And OR Operator
Well, I have a problem. I have to match games by their titles. I use fulltext search for this. It works sufficient, but sometimes gives match to the following games: Jedi Knight and Jedi Knight II To make it correct, I should use IN BOOLEAN MODE flag and write expression like that: Jedi Knight +II This will find only the second part of this game. But there may be a situation, when it is called Jedi Knight 2, and in my DB I have it as Jedi Knight II. So is there any way to compose expression in the way like this: [pseudo code] Jedi Knight +(2|II) So that in included only rows with 2 or II in the title. Is there any way to do so?
View Replies !
Fulltext Search For 3 Characters.
More related to mySQL, but if I want to search for a word with 3 characters in my FULLTEXT field e.g "PHP", "XML" etc. What I am supposed to do? as mySQL is not giving the results for 3 or less than 3 characters.
View Replies !
Weighting A Fulltext Search
I've got a fulltext search where I want to weight one of the columns, but not sure I'm doing it right. If I have 2 separate fulltext indexes I can do this Code: SELECT * FROM ( select prodName,catName, catKeys, subName, subKeys, MATCH(keywords) AGAINST('protector')*3+ MATCH(prodName, catName, catKeys, subName, subKeys) AGAINST ('protector') as score from tProducts WHERE ( MATCH (prodName, catName, catKeys, subName, subKeys) AGAINST ('protector') OR MATCH(keywords) AGAINST('protector') ) ) as results order by score DESC But it's soooooooo much slower than using a single fulltext match. (about 0.5 second compared to 0.005 second) If just use a single fulltext index and match keywords in boolean mode Code: MATCH(keywords) AGAINST('protector' IN BOOLEAN MODE)*3 , its even slower.
View Replies !
FULLTEXT Search IN BOOLEAN MODE
SELECT id, student_first_name, student_last_name, major, minor , MATCH(major, minor) AGAINST ('"mechanical engineering"') AS score FROM students WHERE MATCH(major, minor) AGAINST ('"mechanical engineering"' IN BOOLEAN MODE) GROUP BY id, student_first_name, student_last_name, major, minor, score ORDER BY score DESC This query in MySQL 4.1.12 using MyISAM tables with FULLTEXT index that maps to student.major, student.minor, produces results that do this: 1) Matches major/minor "Mechanical Engineering" 2) Matches major/minor "Mechanical" 3) Matches major/minor "Mechanic" 4) Matches major/minor "Engineering" 5) Matches major/minor "Engineer" I was under the impression, according to this MySQL book I have "MySQL (Third Edition", Paul DuBois, Developer's Library), in Chapter 2, page 197 that if you group your search keyword in double quotes, you will get only the results within double quotes IN BOOLEAN MODE. Apparently this is not happening.
View Replies !
Fulltext Search Not Returning Matches..
my query: SELECT * FROM `links` WHERE MATCH ( KEYWORDS ) AGAINST ( 'paper' ); I have a table with 4 records in it of links to various newspapers, paper and newspaper are both in the keywords field. The keywords field has a fulltext index on it. Did I totally miss a step somewhere? The query doesn't error, I just get 0 results back.
View Replies !
Fulltext Search For Scandinavian Letters
Does newer versions MySQL support words with Scandinavian letters like æ, ø, å, ö, ä etc in full-text searches? The server I'm on runs 3.23.53a. If so, from what version was it supported? And where can I find documentation on it? Can't find anything in the full-text manual.
View Replies !
Punctuation Limitation In Fulltext Search
Can someone please tell me if my problem with the punctuation in a fulltext= search is a bug or limitation? Ultimately the users of the system i built = do not want to have to try using the + search then trying the * search for = all the words which will obviouslly return a lot of results, is there a wor= k around.
View Replies !
Fulltext Search And Column Combinations
I'm doing a fulltext search through a database. I want to allow the user to select the specific columns to be searched. As I understand from the Mysql manual (and from my personal experience) this will require me to create indices for all possible column combinations, which is an uncomfortably large number. My question is, is there any way to solve this problem?
View Replies !
Fulltext Search BOOLEAN Mode
I am trying to implement a very simple search for my site. The only 2 requirements I have are: 1) Results are ordered by relevance 2) Users should be able to use quotes to mark a phrase Whenever I do a fulltext search without 'IN BOOLEAN MODE', I get great relevance, but no ability to use quotes. On the flipside, when I add 'IN BOOLEAN MODE', I can use the quotes, but the relevance goes out the window.
View Replies !
FullText Search :: Place A + Sign
What i need to do is to split the keywords to place a "+" before the keyword (to make the search returns results which MUST contain all keywords) ... I did that succesfully, but when i tried the search, it didn't work well, it works fine form some phrases, but when i tried to search for "just cause" it didn't show any results (there ARE records containing both words) , i tried to search for "just" and also no results ... so whats wrong when searching for "just cause" Code: $qa = preg_replace ("/[^a-z:0-9]/i",'+',$qa); SELECT id, name, match(name) against('+$qa') as score from TABLE where MATCH(name) AGAINST('+$qa' IN BOOLEAN MODE)
View Replies !
FULLTEXT Search Across Multiple Tables
Code: SELECT SQL_CALC_FOUND_ROWS DISTINCT(summ_listings.listing_id), TRIM(summ_listings.listing_name) AS listing_name, TRIM(SUBSTRING(summ_listings.listing_description,1,180)) AS listing_desc, summ_listings.listing_bedrooms, TRIM(summ_listings.listing_estate) AS listing_estate, summ_images.image_thumb, summ_towns.town_name, summ_states.state_name, summ_countries.country_name FROM summ_listings LEFT JOIN summ_images ON summ_images.listing_id = summ_listings.listing_id AND image_default = 1 LEFT JOIN summ_towns ON summ_listings.town_id = summ_towns.town_id LEFT JOIN summ_states ON summ_listings.state_id = summ_states.state_id LEFT JOIN summ_countries ON summ_listings.country_id = summ_countries.country_id WHERE summ_listings.listing_expires > CURRENT_TIMESTAMP ORDER BY summ_listings.listing_last_updated DESC LIMIT 0, 10;
View Replies !
Search Engine :: How To Speed Up Fulltext?
I am running a search engine that uses mySQL fulltext search for search algorithm (after the search being preprocessed a little). Now since my bot indexed already over 200.000 sites the search engine keeps getting slower and slower. Now I checked what is the factor that causes that slower output of search results: It´s obviously the fulltext - search of mySQL. Do there exist options to speeden up that a little?
View Replies !
|
TRACKER
