Spring data jpa predicate hint index for mysql (Jpa + Hibernate)
0
I have an application using Spring data Jpa/Hibernate (Mysql database). I have specific query using Spring data in this way:
myRepository.findAll(filterSpecifications, pageable);
My query is too slow. I need to use index created in MY table to be able to query fast.
from MyTable table FORCE INDEX(Mytable_createdAt_index)
Which is the way to say Jpa force using this index with specifications?
mysql hibernate spring-data-jpa
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
add a comment |
0
I have an application using Spring data Jpa/Hibernate (Mysql database). I have specific query using Spring data in this way:
myRepository.findAll(filterSpecifications, pageable);
My query is too slow. I need to use index created in MY table to be able to query fast.
from MyTable table FORCE INDEX(Mytable_createdAt_index)
Which is the way to say Jpa force using this index with specifications?
mysql hibernate spring-data-jpa
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
1
Why do you think that using an index would be faster when performing selecting all the rows from a table?
– Jens Schauder
Nov 28 '18 at 5:43
I have tested the same query with force index with better response time. Without using this approach took 30 seconds. Using describe command shows MySQL using another index.
– CRISTIAN ROMERO MATESANZ
Nov 28 '18 at 7:21
Oh sorry, completely missed that you are passing specification and pagination. I only saw thefindAllpart.
– Jens Schauder
Nov 28 '18 at 10:10
1
You'd need to provide your query specifically with a @Query annotation, can't be combined with aSpecification. So I guess that leaves you with a custom method assembling the SQL your own. Or maybe you can encode the force index in a view?
– Jens Schauder
Nov 28 '18 at 10:13
add a comment |
0
0
0
I have an application using Spring data Jpa/Hibernate (Mysql database). I have specific query using Spring data in this way:
myRepository.findAll(filterSpecifications, pageable);
My query is too slow. I need to use index created in MY table to be able to query fast.
from MyTable table FORCE INDEX(Mytable_createdAt_index)
Which is the way to say Jpa force using this index with specifications?
mysql hibernate spring-data-jpa
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
I have an application using Spring data Jpa/Hibernate (Mysql database). I have specific query using Spring data in this way:
myRepository.findAll(filterSpecifications, pageable);
My query is too slow. I need to use index created in MY table to be able to query fast.
from MyTable table FORCE INDEX(Mytable_createdAt_index)
Which is the way to say Jpa force using this index with specifications?
mysql hibernate spring-data-jpa
mysql hibernate spring-data-jpa
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
edited Nov 28 '18 at 11:37
CRISTIAN ROMERO MATESANZ
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
asked Nov 27 '18 at 20:50
CRISTIAN ROMERO MATESANZCRISTIAN ROMERO MATESANZ
483313
483313
1
Why do you think that using an index would be faster when performing selecting all the rows from a table?
– Jens Schauder
Nov 28 '18 at 5:43
I have tested the same query with force index with better response time. Without using this approach took 30 seconds. Using describe command shows MySQL using another index.
– CRISTIAN ROMERO MATESANZ
Nov 28 '18 at 7:21
Oh sorry, completely missed that you are passing specification and pagination. I only saw thefindAllpart.
– Jens Schauder
Nov 28 '18 at 10:10
1
You'd need to provide your query specifically with a @Query annotation, can't be combined with aSpecification. So I guess that leaves you with a custom method assembling the SQL your own. Or maybe you can encode the force index in a view?
– Jens Schauder
Nov 28 '18 at 10:13
add a comment |
1
Why do you think that using an index would be faster when performing selecting all the rows from a table?
– Jens Schauder
Nov 28 '18 at 5:43
I have tested the same query with force index with better response time. Without using this approach took 30 seconds. Using describe command shows MySQL using another index.
– CRISTIAN ROMERO MATESANZ
Nov 28 '18 at 7:21
Oh sorry, completely missed that you are passing specification and pagination. I only saw thefindAllpart.
– Jens Schauder
Nov 28 '18 at 10:10
1
You'd need to provide your query specifically with a @Query annotation, can't be combined with aSpecification. So I guess that leaves you with a custom method assembling the SQL your own. Or maybe you can encode the force index in a view?
– Jens Schauder
Nov 28 '18 at 10:13
1
1
Why do you think that using an index would be faster when performing selecting all the rows from a table?
– Jens Schauder
Nov 28 '18 at 5:43
Why do you think that using an index would be faster when performing selecting all the rows from a table?
– Jens Schauder
Nov 28 '18 at 5:43
I have tested the same query with force index with better response time. Without using this approach took 30 seconds. Using describe command shows MySQL using another index.
– CRISTIAN ROMERO MATESANZ
Nov 28 '18 at 7:21
I have tested the same query with force index with better response time. Without using this approach took 30 seconds. Using describe command shows MySQL using another index.
– CRISTIAN ROMERO MATESANZ
Nov 28 '18 at 7:21
Oh sorry, completely missed that you are passing specification and pagination. I only saw the
findAll part.– Jens Schauder
Nov 28 '18 at 10:10
Oh sorry, completely missed that you are passing specification and pagination. I only saw the
findAll part.– Jens Schauder
Nov 28 '18 at 10:10
1
1
You'd need to provide your query specifically with a @Query annotation, can't be combined with a
Specification. So I guess that leaves you with a custom method assembling the SQL your own. Or maybe you can encode the force index in a view?– Jens Schauder
Nov 28 '18 at 10:13
You'd need to provide your query specifically with a @Query annotation, can't be combined with a
Specification. So I guess that leaves you with a custom method assembling the SQL your own. Or maybe you can encode the force index in a view?– Jens Schauder
Nov 28 '18 at 10:13
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53507937%2fspring-data-jpa-predicate-hint-index-for-mysql-jpa-hibernate%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
draft saved
draft discarded
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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53507937%2fspring-data-jpa-predicate-hint-index-for-mysql-jpa-hibernate%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
1
Why do you think that using an index would be faster when performing selecting all the rows from a table?
– Jens Schauder
Nov 28 '18 at 5:43
I have tested the same query with force index with better response time. Without using this approach took 30 seconds. Using describe command shows MySQL using another index.
– CRISTIAN ROMERO MATESANZ
Nov 28 '18 at 7:21
Oh sorry, completely missed that you are passing specification and pagination. I only saw the
findAllpart.– Jens Schauder
Nov 28 '18 at 10:10
1
You'd need to provide your query specifically with a @Query annotation, can't be combined with a
Specification. So I guess that leaves you with a custom method assembling the SQL your own. Or maybe you can encode the force index in a view?– Jens Schauder
Nov 28 '18 at 10:13