Sfrgttk

How can I automatically replace [[ and ]] with the [LeftDoubleBracket] and [RightDoubleBracket] operators?

How can bays and straits be determined in a procedurally generated map?

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

Can I make popcorn with any corn?

Do Phineas and Ferb ever actually get busted in real time?

How is it possible for user to changed after storage was encrypted? (on OS X, Android)

Why don't electromagnetic waves interact with each other?

A function which translates a sentence to title-case

declaring a variable twice in IIFE

Example of a relative pronoun

Set-theoretical foundations of Mathematics with only bounded quantifiers

Pronouncing Dictionary.com's W.O.D "vade mecum" in English

Patience, young "Padovan"

"which" command doesn't work / path of Safari?

What makes Graph invariants so useful/important?

What are these boxed doors outside store fronts in New York?

New order #4: World

I probably found a bug with the sudo apt install function

Are tax years 2016 & 2017 back taxes deductible for tax year 2018?

Compute hash value according to multiplication method

Download, install and reboot computer at night if needed

How can the DM most effectively choose 1 out of an odd number of players to be targeted by an attack or effect?

Question about Goedel's incompleteness Proof

Do airline pilots ever risk not hearing communication directed to them specifically, from traffic controllers?

Performance problem using mysql fulltext search

No results for match…against in boolean mode search with fulltext index (MySQL)Mysql fulltext boolean ignore phraseFULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditionalSQL Server Fulltext search against big amount of search terms over some period of timeMysql fulltext search my.cnf optimizationwhy/how does the number of matched columns influences the way of excecuting a queryMySQL Fulltext match with forward slashesmysql match against boolean modeWhich database technologies to consider for a data search application?MySQL FullText search on string shorter than 3 chars returns no rows

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

4

I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application.

There are 500 000 rows in the table. The MySQL version is 5.7.19.

Most, if not all rows begins with 'c:data' (ie: 'c:dataclientsmicrosoftprivateemails.xls')

When I perform a search for 'microsoft private emails', ie;

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`

everything is fine and I get the results in less than a second.

When I perform a search with the root of the file path, ie;

MATCH(file_path) AGAINST('"c:\data\clients"' IN BOOLEAN MODE) 

-- note that it's an exact match with quotes ""

... the query runs forever (443 seconds last time I waited long enough), and the CPU hits 100% during the time of the query.

I use a LIMIT 0, 10 on both queries. The problem happens both with MyISAM and Innodb.

In fact, I have to execute $ service mysql restart to get things back to normal, otherwise the whole server becomes more-or-less responsive.

My question is:

Why is this happening and is there a way to fix it?

My theory, and I have very little knowledge about how the fulltext index works, is the fact that c:data is in every row, the index fails and it's somehow doing a full table scan but with so many '' in every row, it causes mysql to just loose it.

If I am right about the '' thing, how is it possible to search for absolute file paths with mysql's full text index?

I hope I provided enough details.

mysql performance full-text-search

share|improve this question

edited Aug 1 '17 at 14:28

joanolo

9,91842254

asked Jul 31 '17 at 17:17

MaxMax

211

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How many rows are returned for first and second query?
  
  – Lennart
  Jul 31 '17 at 17:24
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @MaximeLabelle please see dba.stackexchange.com/help/merging-accounts
  
  – Michael Green
  Jul 31 '17 at 17:44
  

  
  
  
  

add a comment | 

4

I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application.

There are 500 000 rows in the table. The MySQL version is 5.7.19.

Most, if not all rows begins with 'c:data' (ie: 'c:dataclientsmicrosoftprivateemails.xls')

When I perform a search for 'microsoft private emails', ie;

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`

everything is fine and I get the results in less than a second.

When I perform a search with the root of the file path, ie;

MATCH(file_path) AGAINST('"c:\data\clients"' IN BOOLEAN MODE) 

-- note that it's an exact match with quotes ""

... the query runs forever (443 seconds last time I waited long enough), and the CPU hits 100% during the time of the query.

I use a LIMIT 0, 10 on both queries. The problem happens both with MyISAM and Innodb.

In fact, I have to execute $ service mysql restart to get things back to normal, otherwise the whole server becomes more-or-less responsive.

My question is:

Why is this happening and is there a way to fix it?

My theory, and I have very little knowledge about how the fulltext index works, is the fact that c:data is in every row, the index fails and it's somehow doing a full table scan but with so many '' in every row, it causes mysql to just loose it.

If I am right about the '' thing, how is it possible to search for absolute file paths with mysql's full text index?

I hope I provided enough details.

mysql performance full-text-search

share|improve this question

edited Aug 1 '17 at 14:28

joanolo

9,91842254

asked Jul 31 '17 at 17:17

MaxMax

211

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How many rows are returned for first and second query?
  
  – Lennart
  Jul 31 '17 at 17:24
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @MaximeLabelle please see dba.stackexchange.com/help/merging-accounts
  
  – Michael Green
  Jul 31 '17 at 17:44
  

  
  
  
  

add a comment | 

I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application.

There are 500 000 rows in the table. The MySQL version is 5.7.19.

Most, if not all rows begins with 'c:data' (ie: 'c:dataclientsmicrosoftprivateemails.xls')

When I perform a search for 'microsoft private emails', ie;

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`

everything is fine and I get the results in less than a second.

When I perform a search with the root of the file path, ie;

MATCH(file_path) AGAINST('"c:\data\clients"' IN BOOLEAN MODE) 

-- note that it's an exact match with quotes ""

... the query runs forever (443 seconds last time I waited long enough), and the CPU hits 100% during the time of the query.

I use a LIMIT 0, 10 on both queries. The problem happens both with MyISAM and Innodb.

In fact, I have to execute $ service mysql restart to get things back to normal, otherwise the whole server becomes more-or-less responsive.

My question is:

Why is this happening and is there a way to fix it?

My theory, and I have very little knowledge about how the fulltext index works, is the fact that c:data is in every row, the index fails and it's somehow doing a full table scan but with so many '' in every row, it causes mysql to just loose it.

If I am right about the '' thing, how is it possible to search for absolute file paths with mysql's full text index?

I hope I provided enough details.

mysql performance full-text-search

share|improve this question

edited Aug 1 '17 at 14:28

joanolo

9,91842254

asked Jul 31 '17 at 17:17

MaxMax

211

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`

MATCH(file_path) AGAINST('"c:\data\clients"' IN BOOLEAN MODE) 

-- note that it's an exact match with quotes ""

share|improve this question

edited Aug 1 '17 at 14:28

joanolo

9,91842254

asked Jul 31 '17 at 17:17

MaxMax

211

share|improve this question

edited Aug 1 '17 at 14:28

joanolo

9,91842254

asked Jul 31 '17 at 17:17

MaxMax

211

edited Aug 1 '17 at 14:28

joanolo

9,91842254

edited Aug 1 '17 at 14:28

joanolo

9,91842254

asked Jul 31 '17 at 17:17

MaxMax

211

asked Jul 31 '17 at 17:17

MaxMax

211

2 Answers
2

active

oldest

votes

0

Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.

share|improve this answer

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

add a comment | 

0

For an exact match, use '=', not FULLTEXT:

INDEX(file_path) -- in addition to `FULLTEXT(file_path)`



WHERE file_path = "c:\data\clients"  -- instead of MATCH...

share|improve this answer

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f182284%2fperformance-problem-using-mysql-fulltext-search%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.

share|improve this answer

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

add a comment | 

0

Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.

share|improve this answer

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

add a comment | 

Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.

share|improve this answer

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

share|improve this answer

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

answered Aug 1 '17 at 17:01

Maxime LabelleMaxime Labelle

71

0

For an exact match, use '=', not FULLTEXT:

INDEX(file_path) -- in addition to `FULLTEXT(file_path)`



WHERE file_path = "c:\data\clients"  -- instead of MATCH...

share|improve this answer

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259

add a comment | 

0

For an exact match, use '=', not FULLTEXT:

INDEX(file_path) -- in addition to `FULLTEXT(file_path)`



WHERE file_path = "c:\data\clients"  -- instead of MATCH...

share|improve this answer

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259

add a comment | 

For an exact match, use '=', not FULLTEXT:

INDEX(file_path) -- in addition to `FULLTEXT(file_path)`



WHERE file_path = "c:\data\clients"  -- instead of MATCH...

share|improve this answer

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259

INDEX(file_path) -- in addition to `FULLTEXT(file_path)`



WHERE file_path = "c:\data\clients"  -- instead of MATCH...

share|improve this answer

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259

answered Aug 13 '17 at 14:00

Rick JamesRick James

43.8k22259