0
votes
1answer
50 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
0
votes
0answers
101 views

MySQL Pre-Filter Subquery

Good Evening All, I'm trying to pre-filter a table such that the more complex MATCH ... AGAINST query doesn't need to process every row in the DB. I tried obtaining a list of identifiers (query 3) ...
1
vote
1answer
251 views

MySQL Regex matching newline or end of string

I have a text column in mysql which contains some values separated by a newline. I want now to query by these values and thought a regex should help me with that by doing something like: SELECT * ...
0
votes
0answers
35 views

fulltext search on multiple tables with different fields

I am trying to make a search page return results from multiple table ordered by the keyword. The problem is the select statement gives a result sorted by the select statement and not by the keyword. ...
0
votes
1answer
70 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
1
vote
1answer
515 views

mysql match against boolean mode

I'm using MATCH AGAINST in boolean mode to add a simple search functionality to my site. The column to be searched is varchar, with no full text index. The column value can be alphanumeric, all ...
2
votes
1answer
116 views

Sphinx: Indexer causes MySQL to hang

Not sure what's going on. I run indexer --all --rotate When it finishes mysql hangs and not accepting new connections. from my observation as soon as indexer finishes, all update,insert,delete ...
1
vote
0answers
98 views

Fulltext search returning too many rows

Server: MySQL 5.1.66-0+squeeze1 My problem: I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution ...
5
votes
1answer
271 views

Full text search through many tables in MySQL

We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its ...
1
vote
0answers
107 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
1
vote
2answers
1k views

MYSQL matching one column to another in a different table via a FULLTEXT index

I current have two different tables. The first table has a list of titles and IDs associated to these titles, the second table is a list of random heading. What I would like to know is if there is a ...
1
vote
1answer
91 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
2
votes
2answers
579 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
2answers
336 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
2answers
133 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
141 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
267 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 ...
0
votes
1answer
530 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
42 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, ...
1
vote
1answer
557 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 ...
3
votes
2answers
2k 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
1answer
153 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
516 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
162 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
543 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
113 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
58 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
622 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
278 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
114 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
2k 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
185 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*' ...
3
votes
3answers
1k 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 ...
5
votes
1answer
471 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
253 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
5k 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
1k 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
523 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
254 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
3k 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
391 views

MySQL Full Text index issue

I Have a table articles with following data mysql> select * from articles; +----+-----------------------+------------------------------------------+ | id | title | body ...
1
vote
1answer
243 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
2k 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
719 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
357 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 ...
9
votes
3answers
1k 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 ...