Tagged Questions
2
votes
2answers
59 views
MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'
I'm trying to understand FULLTEXT indexes.
Is there some SQL query to test 'ft_min_word_len' variable?
I've tried to match four characters only in the example below but it doesn't work. Why the ...
2
votes
1answer
36 views
MySQL Full Text search increase relevance for exact matches?
I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching ...
0
votes
0answers
38 views
mySQL- large database
I need guideline to setup DBMS(MYSQL or SQL Server 2008). The database size is expected upto 80-100GB in next few months. One main table will contain textual content like essays. Can MySQL handle such ...
0
votes
1answer
29 views
MySQL not allowing text columns?
I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE ...
0
votes
2answers
73 views
Bypass MyISAM table lock
I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
1
vote
1answer
70 views
I would like to query a range of criteria on multiple columns in MySQL
I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :
We have 3 columns as ...
0
votes
1answer
88 views
MySQL Fulltext match with forward slashes
I'm trying to run a fulltext match against a product database, where the product names have forward slashes in them. These are staple sizes, so it's quite important that they get matched.
However, ...
0
votes
1answer
24 views
Which text-index I should create for xooops engine to achieve better search results?
In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following :
SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
0
votes
1answer
77 views
Why does MATCH() give Score 0, if there are less rows in the database?
I followed this example from the Mysql Documentation:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT ...
2
votes
2answers
181 views
How to search for a specific column name in all the tables in MySQL Workbench?
In MySQL Workbench, is it possible to search for a specific column name in all the tables?
(Writing the string to look for in the field at the top right does nothing).
Thank you.
0
votes
0answers
153 views
Slow INSERT INTO SELECT statement – not repeatable!
Short Version:
INSERT INTO SELECT into a table with fulltext index with the same data takes sometimes 25 seconds and sometimes 2500 seconds. We have no idea where this huge gap is coming from.
Long ...
0
votes
1answer
76 views
How to avoid certain characters in MySQL full text search?
I have a column named parcel_number of varchar type. It contains values like ab-cd-ef and uvwxyz. A search for abcdef or uv-wx-yz does not return those records.
How should I write the query to meet ...
1
vote
2answers
244 views
Slow insert with MySQL full-text index
I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right?
...
3
votes
1answer
101 views
Can underscore be forced as a word splitter without a full-text parser plugin?
Is there any way in MySQL to force underscore to become a word splitter character (like space or dash) without having to use a fulltext parser plugin?
If not, does such a plugin already exist or do I ...
1
vote
2answers
309 views
Slow mysql fulltext query
I have a little problem with the performance of a mysql-query which uses a fulltext index.
The following query
SELECT Mention.id
FROM mentions AS Mention
WHERE (MATCH (`Mention`.`title_text`, ...
2
votes
1answer
90 views
MySQL full text search result problem
I have this query that does not return any results:
SELECT
MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha') AS Score
, e.*
FROM
employees AS e
WHERE
MATCH (e.firstname, ...
0
votes
1answer
56 views
Searching through lots of applications
Google search has instant search feature. While you are typing, it finds the pages. Apple.com search box. StackExchange ask question part finds similar questions while you are typing question title.
...
5
votes
1answer
345 views
Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?
I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
1
vote
1answer
194 views
How to match on a longer word using MySQL Full Text Search?
I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter)
When I search something, it will first search in the company ...
2
votes
1answer
91 views
why/how does the number of matched columns influences the way of excecuting a query
Imagine the following situation:
Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index.
FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4
Table B uses InnoDB and ...
3
votes
1answer
585 views
Why full-text-search returns less rows than LIKE
I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists.
Example statements:
SELECT `meldungstext`
FROM `artikel`
WHERE `meldungstext` LIKE ...
0
votes
1answer
71 views
Why number of matches in fulltext search get less when we put less strings?
SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('resta*' IN NATURAL LANGUAGE MODE)
show 5 results
SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('restaurant*' ...
2
votes
3answers
710 views
slow query with fulltext and left join
I have 3 tables
about 250,000 records.
table1 is MyISAM, add fullindex with title and content, add index with pid.
table2 and table3 are InnoDB, add index with pid.
Only query table3, just cost ...
0
votes
0answers
78 views
Database structure advice for large data text searching
I want to search scanned text (comprised of a few gigabytes of text).
The problem with this text (which is why I cannot make an index of words) is that it was OCRed, so there could be (and are) ...
4
votes
1answer
236 views
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
I have a basic table :
create table fullTextTest
(
id INT(11) NOT NULL,
superText CHAR(255) NOT NULL,
superLongText TEXT NOT NULL,
primary key (`id`),
FULLTEXT KEY `superText` ...
0
votes
0answers
118 views
What's is WITH QUERY EXPANSION MODE in MySQL Fulltext Search
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY ...
4
votes
1answer
2k views
Why is LIKE more than 4x faster than MATCH…AGAINST on a FULLTEXT index in MySQL?
I'm not getting this.
I've got a table with these indexes
PRIMARY post_id
INDEX topic_id
FULLTEXT post_text
Table has (only) 346 000 rows. I am trying to perform 2 queries.
SELECT ...
2
votes
1answer
939 views
Best way to compare strings using pre-built ngrams?
I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped ...
2
votes
3answers
335 views
Which database is best for deeply embedded database or through C DLL?
I want a deeply embedded database. Deeply embedded means that the server is started by an application and closed by the application with no tcp/ip or 0 port. The main features of consideration are:
...
5
votes
2answers
222 views
Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search
Followings are what I need to do on my project:
1- If a search term is new and unique, then add the search term
2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
3
votes
1answer
2k views
Mysql fulltext search my.cnf optimization
I have open a question on http://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here.
We built a news site. Every day we will input tens of ...
2
votes
2answers
264 views
MySQL Full Text index issue
I Have a table articles with following data
mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title | body ...
0
votes
1answer
91 views
Where I can find articles or resources explain MySQL Fulltext index? [closed]
I want to understand MySQL index and Fulltext index. Where can I find resources to help me understand it? Or can you help understand MySQL indexing mechanism?
1
vote
1answer
198 views
MySQL full text search ranking
I found in this article the formula that MySQL use to ranking in FTS
w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf)
Where
dtf is the number of times the term appears in the ...
6
votes
2answers
1k views
Why does MyISAM support FULLTEXT Searching and InnoDB does not?
I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
3
votes
1answer
523 views
FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
fulltext match is ignoring its index when I add a number of words conditional for its boolean mode. The selects are as follows:
explain select * from seeds WHERE MATCH(text) AGAINST ("mount ...
3
votes
1answer
321 views
Mysql fulltext boolean ignore phrase
I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business".
Something like the following is the general idea but ...
8
votes
3answers
681 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
