(mysql(mariadb 10.x.x)) Where match against with fulltext and Order by primary key say 'using filesort'
I'm DB developer
when I tested my selecting query, result said that 'using where;using filesort'
I know that 'using where' is fine, but problem is 'using filesort'
Briefly, my table that is named 'QUESTION' has the structure like below.
QUESTION_IDX(PK) | TITLE(FULLTEXT) | REG_DATE
and my select query is like this below..
SELECT QUESTION_IDX
FROM 'QUESTION'
WHERE MATCH(TITLE) AGAINST('abc' IN BOOLEAN MODE)
ORDER BY QUESTION_IDX DESC
LIMIT 0,18
this query say that
TITLE -> 'Using where; Using filesort'
please inform me the solution removing filesort
Thanks
mysql sql-order-by where
asked Nov 12 '18 at 2:11
PelConn
614
add a comment |
I'm DB developer
when I tested my selecting query, result said that 'using where;using filesort'
I know that 'using where' is fine, but problem is 'using filesort'
Briefly, my table that is named 'QUESTION' has the structure like below.
QUESTION_IDX(PK) | TITLE(FULLTEXT) | REG_DATE
and my select query is like this below..
SELECT QUESTION_IDX
FROM 'QUESTION'
WHERE MATCH(TITLE) AGAINST('abc' IN BOOLEAN MODE)
ORDER BY QUESTION_IDX DESC
LIMIT 0,18
this query say that
TITLE -> 'Using where; Using filesort'
please inform me the solution removing filesort
Thanks
mysql sql-order-by where
asked Nov 12 '18 at 2:11
PelConn
614
test is using 'EXPLAIN' query
– PelConn
Nov 12 '18 at 2:12
'Using filesort' isn't necessary bad. It doesn't always translate into an on disk activity as sorts can be done in memory. Thefilelocation is largely historical where sorts where always on disk.
– danblack
Nov 12 '18 at 2:59
Probably should of mentioned, its currently not possible to do a FT search and have an order by using a different index on table within a query..
– danblack
Nov 13 '18 at 6:06
add a comment |
I'm DB developer
when I tested my selecting query, result said that 'using where;using filesort'
I know that 'using where' is fine, but problem is 'using filesort'
Briefly, my table that is named 'QUESTION' has the structure like below.
QUESTION_IDX(PK) | TITLE(FULLTEXT) | REG_DATE
and my select query is like this below..
SELECT QUESTION_IDX
FROM 'QUESTION'
WHERE MATCH(TITLE) AGAINST('abc' IN BOOLEAN MODE)
ORDER BY QUESTION_IDX DESC
LIMIT 0,18
this query say that
TITLE -> 'Using where; Using filesort'
please inform me the solution removing filesort
Thanks
mysql sql-order-by where
asked Nov 12 '18 at 2:11
PelConn
614
I'm DB developer
when I tested my selecting query, result said that 'using where;using filesort'
I know that 'using where' is fine, but problem is 'using filesort'
Briefly, my table that is named 'QUESTION' has the structure like below.
QUESTION_IDX(PK) | TITLE(FULLTEXT) | REG_DATE
and my select query is like this below..
SELECT QUESTION_IDX
FROM 'QUESTION'
WHERE MATCH(TITLE) AGAINST('abc' IN BOOLEAN MODE)
ORDER BY QUESTION_IDX DESC
LIMIT 0,18
this query say that
TITLE -> 'Using where; Using filesort'
please inform me the solution removing filesort
Thanks
mysql sql-order-by where
mysql sql-order-by where
asked Nov 12 '18 at 2:11
PelConn
614
asked Nov 12 '18 at 2:11
PelConn
614
asked Nov 12 '18 at 2:11
PelConn
614
asked Nov 12 '18 at 2:11
PelConn
614
asked Nov 12 '18 at 2:11
PelConn
614
614
test is using 'EXPLAIN' query
– PelConn
Nov 12 '18 at 2:12
'Using filesort' isn't necessary bad. It doesn't always translate into an on disk activity as sorts can be done in memory. Thefilelocation is largely historical where sorts where always on disk.
– danblack
Nov 12 '18 at 2:59
Probably should of mentioned, its currently not possible to do a FT search and have an order by using a different index on table within a query..
– danblack
Nov 13 '18 at 6:06
add a comment |
test is using 'EXPLAIN' query
– PelConn
Nov 12 '18 at 2:12
'Using filesort' isn't necessary bad. It doesn't always translate into an on disk activity as sorts can be done in memory. Thefilelocation is largely historical where sorts where always on disk.
– danblack
Nov 12 '18 at 2:59
Probably should of mentioned, its currently not possible to do a FT search and have an order by using a different index on table within a query..
– danblack
Nov 13 '18 at 6:06
test is using 'EXPLAIN' query
– PelConn
Nov 12 '18 at 2:12
test is using 'EXPLAIN' query
– PelConn
Nov 12 '18 at 2:12
'Using filesort' isn't necessary bad. It doesn't always translate into an on disk activity as sorts can be done in memory. The
file location is largely historical where sorts where always on disk.– danblack
Nov 12 '18 at 2:59
'Using filesort' isn't necessary bad. It doesn't always translate into an on disk activity as sorts can be done in memory. The
file location is largely historical where sorts where always on disk.– danblack
Nov 12 '18 at 2:59
Probably should of mentioned, its currently not possible to do a FT search and have an order by using a different index on table within a query..
– danblack
Nov 13 '18 at 6:06
Probably should of mentioned, its currently not possible to do a FT search and have an order by using a different index on table within a query..
– danblack
Nov 13 '18 at 6:06
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53255192%2fmysqlmariadb-10-x-x-where-match-against-with-fulltext-and-order-by-primary-k%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53255192%2fmysqlmariadb-10-x-x-where-match-against-with-fulltext-and-order-by-primary-k%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
test is using 'EXPLAIN' query
– PelConn
Nov 12 '18 at 2:12
'Using filesort' isn't necessary bad. It doesn't always translate into an on disk activity as sorts can be done in memory. The
filelocation is largely historical where sorts where always on disk.– danblack
Nov 12 '18 at 2:59
Probably should of mentioned, its currently not possible to do a FT search and have an order by using a different index on table within a query..
– danblack
Nov 13 '18 at 6:06