Linked Questions

13 votes
4 answers
16k views

Full text search results in a large amount of time spent in 'FULLTEXT initialization'

I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like: CREATE TABLE `socomments` ( `Id` int(11) NOT NULL, `PostId` int(11) ...
hichris123's user avatar
13 votes
1 answer
43k 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 ...
genesis's user avatar
  • 319
6 votes
2 answers
2k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
user4951's user avatar
  • 1,365
5 votes
3 answers
9k 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.04 ...
cj333's user avatar
  • 189
4 votes
2 answers
5k views

Fulltext search using multiple indexes

I have a table with several columns, say a, b, c, d that should be searchable. The problem arises when I need to search a, b, c separately from d (and vise-versa). AFAIK, there's no way to achieve ...
bobo's user avatar
  • 43
2 votes
2 answers
4k 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 ...
HTF's user avatar
  • 459
6 votes
2 answers
3k 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` (`...
Ant's user avatar
  • 161
6 votes
2 answers
636 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM `...
user4951's user avatar
  • 1,365
0 votes
1 answer
3k views

Mysql Fulltext search doesn't return any results

I have an issue with a full text search in a MySQL table. MATCH AGAINST returns no results even I if have 7 records containing the words I'm looking for. What can I do to make it return the rows? ...
Emanuel's user avatar
  • 263
0 votes
2 answers
2k views

Is inserting in a MySQL table with a Full-Text index really slow?

Reading the documentation, I see this mention : For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load ...
Yanick Rochon's user avatar
3 votes
2 answers
1k views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
monamona's user avatar
  • 285
0 votes
1 answer
1k views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
Jim's user avatar
  • 761
1 vote
2 answers
360 views

Will moving the MATCH...AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance?

Basically, I have the following query: select `t`.* from `tracking` `t` where exists ( select `ti`.`id` from `tracking_items` `ti` inner join `cars` `c` on (`c`.`car_id` = `ti`.`...
Kristian Vitozev's user avatar