Sfrgttk

How to deal with a cynical class?

Can the damage from a Talisman of Pure Good (or Ultimate Evil) be non-lethal?

What options are left, if Britain cannot decide?

Is it normal that my co-workers at a fitness company criticize my food choices?

Fill color and outline color with the same value

How Did the Space Junk Stay in Orbit in Wall-E?

Welcoming 2019 Pi day: How to draw the letter π?

Why are there 40 737 Max planes in flight when they have been grounded as not airworthy?

SQL Server Primary Login Restrictions

Bash replace string at multiple places in a file from command line

Schematic conventions for different supply rails

Why using two cd commands in bash script does not execute the second command

What has been your most complicated TikZ drawing?

How to answer questions about my characters?

Bastion server: use TCP forwarding VS placing private key on server

My story is written in English, but is set in my home country. What language should I use for the dialogue?

Professor being mistaken for a grad student

In-house repeater?

Provisioning profile doesn't include the application-identifier and keychain-access-groups entitlements

Russian cases: A few examples, I'm really confused

Know when to turn notes upside-down(eighth notes, sixteen notes, etc.)

Counting certain elements in lists

Official degrees of earth’s rotation per day

RegionDifference for Cylinder and Cuboid

The performance of mysql fulltext depend on order of keyword

why/how does the number of matched columns influences the way of excecuting a queryCalculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLEAvoiding performance hit from GROUP BY during FULLTEXT search?Skipping stopwords (and indexing small words) for one fulltext index only, in MySQLLevenshtein vs MATCH vs Others for best MySQL string matchOptimized MySQL “optional” FULLTEXT search on multiple tablesFulltext match against a number doesn't return any resultsHow to make a FULLTEXT search with ORDER BY fast?MySQL Fulltext indices slow after power outageUsing MATCH, AGAINST multiple times in MySQL fulltext search

0

I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result:

1. 
   

   FAST (1s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE)
   
   

   
   


2. 
   

   SLOW (10s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE)
   
   

   
   


3. 
   

   FAST (1s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)
   
   AND    (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE))
   
   

   
   


4. 
   

   SLOW (10s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE))
   
   AND    (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE))
   
   

   
   

How can I optimize it?

mysql query-performance full-text-search

share|improve this question

edited May 29 '17 at 7:56

Marco

3,74231724

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

bumped to the homepage by Community♦ 5 mins ago

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

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  MyISAM? or InnoDB? Which version of MySQL? What is the value of the setting for min word len?
  
  – Rick James
  May 29 '17 at 15:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RickJames I am using InnoDB, version 5.7. This is longtext field.
  
  – hoangvu68
  May 30 '17 at 6:41
  

  
  
  
  

add a comment | 

0

I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result:

1. 
   

   FAST (1s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE)
   
   

   
   


2. 
   

   SLOW (10s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE)
   
   

   
   


3. 
   

   FAST (1s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)
   
   AND    (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE))
   
   

   
   


4. 
   

   SLOW (10s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE))
   
   AND    (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE))
   
   

   
   

How can I optimize it?

mysql query-performance full-text-search

share|improve this question

edited May 29 '17 at 7:56

Marco

3,74231724

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

bumped to the homepage by Community♦ 5 mins ago

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

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  MyISAM? or InnoDB? Which version of MySQL? What is the value of the setting for min word len?
  
  – Rick James
  May 29 '17 at 15:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RickJames I am using InnoDB, version 5.7. This is longtext field.
  
  – hoangvu68
  May 30 '17 at 6:41
  

  
  
  
  

add a comment | 

I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result:

1. 
   

   FAST (1s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE)
   
   

   
   


2. 
   

   SLOW (10s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE)
   
   

   
   


3. 
   

   FAST (1s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)
   
   AND    (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE))
   
   

   
   


4. 
   

   SLOW (10s)

   
   
   

   SELECT *
   
   FROM   table
   
   WHERE  (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE))
   
   AND    (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE))
   
   

   
   

How can I optimize it?

mysql query-performance full-text-search

share|improve this question

edited May 29 '17 at 7:56

Marco

3,74231724

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

share|improve this question

edited May 29 '17 at 7:56

Marco

3,74231724

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

share|improve this question

edited May 29 '17 at 7:56

Marco

3,74231724

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

edited May 29 '17 at 7:56

Marco

3,74231724

edited May 29 '17 at 7:56

Marco

3,74231724

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

asked May 29 '17 at 7:49

hoangvu68hoangvu68

1012

1 Answer
1

active

oldest

votes

0

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE `table`;

Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .

share|improve this answer

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259

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%2f174804%2fthe-performance-of-mysql-fulltext-depend-on-order-of-keyword%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE `table`;

Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .

share|improve this answer

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259

add a comment | 

0

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE `table`;

Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .

share|improve this answer

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259

add a comment | 

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE `table`;

Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .

share|improve this answer

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259

set GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE `table`;

share|improve this answer

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259

answered May 30 '17 at 14:50

Rick JamesRick James

43.4k22259