MariaDB server running 10 x faster than anything we can reproduce. Ideas why?
we've got a site on cheap, shared hosting.
They've got a MariaDB server, which I presume is shared by many customers. We run some pretty chunky queries on this and get fast responses. We want to move the hosting elsewhere, but we simply can't get a database running anywhere near as fast.
We've tried on a new VPS server, with SSD, 12 cores and 16 gigs ram. Queries are taking 10 times longer than the shared hosting's db.
(In fact, increasing number of cores made little difference). We tried MySQL and MariaDB + our hosts also setup lightspeed, memcache and Redis, but they didn't help.
For a specific query I've been testing with, the shared server takes approx 0.7 seconds to run, the new server takes about 8 seconds.
We've checked that the indexes are in place (the EXPLAIN query looks the same on both), and we're confident that the whole query isn't cached because minor changes to the query don't effect speed. The output is tiny, so it's the processing that's slower.
The slow queries are long and wide ranging, but effectively it's when it's inefficiently trying to match rows where an ID is saved as a varchar. eg.
SELECT post.ID
FROM post
LEFT JOIN meta
ON post.ID = meta.postID
AND meta.key = "ABC"
WHERE post.type = "XYZ"
AND meta.value = "123456";
An EXPLAIN on a slow query reveals:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, _booking_all_day, ref, "post_id,meta_key", meta_key, 576, const, 71474, Using where; Using temporary; Using filesort
....
I've pasted the SHOW VARIABLES for each server here:
https://pastebin.com/iW6WL3zC - fast, shared server.
https://pastebin.com/kJGKHyqs - new, slower server.
I have a decent amount of experience with MySQL, but I'm no db engineer! This is my first time with a MariaDB so I don't know if there are important optimisations that I'm unaware of.
One obvious answer is re-write the bad SQL code. We will do, but we will need to move the hosting before we have time to do that. This question is just on why the same query could be so different / any recommendations for trying to get the new server running as fast (or even faster)! Thanks.
sql linux mariadb
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
|
show 8 more comments
we've got a site on cheap, shared hosting.
They've got a MariaDB server, which I presume is shared by many customers. We run some pretty chunky queries on this and get fast responses. We want to move the hosting elsewhere, but we simply can't get a database running anywhere near as fast.
We've tried on a new VPS server, with SSD, 12 cores and 16 gigs ram. Queries are taking 10 times longer than the shared hosting's db.
(In fact, increasing number of cores made little difference). We tried MySQL and MariaDB + our hosts also setup lightspeed, memcache and Redis, but they didn't help.
For a specific query I've been testing with, the shared server takes approx 0.7 seconds to run, the new server takes about 8 seconds.
We've checked that the indexes are in place (the EXPLAIN query looks the same on both), and we're confident that the whole query isn't cached because minor changes to the query don't effect speed. The output is tiny, so it's the processing that's slower.
The slow queries are long and wide ranging, but effectively it's when it's inefficiently trying to match rows where an ID is saved as a varchar. eg.
SELECT post.ID
FROM post
LEFT JOIN meta
ON post.ID = meta.postID
AND meta.key = "ABC"
WHERE post.type = "XYZ"
AND meta.value = "123456";
An EXPLAIN on a slow query reveals:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, _booking_all_day, ref, "post_id,meta_key", meta_key, 576, const, 71474, Using where; Using temporary; Using filesort
....
I've pasted the SHOW VARIABLES for each server here:
https://pastebin.com/iW6WL3zC - fast, shared server.
https://pastebin.com/kJGKHyqs - new, slower server.
I have a decent amount of experience with MySQL, but I'm no db engineer! This is my first time with a MariaDB so I don't know if there are important optimisations that I'm unaware of.
One obvious answer is re-write the bad SQL code. We will do, but we will need to move the hosting before we have time to do that. This question is just on why the same query could be so different / any recommendations for trying to get the new server running as fast (or even faster)! Thanks.
sql linux mariadb
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
Nov 19 '18 at 10:43
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
Nov 19 '18 at 10:45
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
Nov 19 '18 at 11:47
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
Nov 19 '18 at 13:23
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
Nov 19 '18 at 13:25
|
show 8 more comments
we've got a site on cheap, shared hosting.
They've got a MariaDB server, which I presume is shared by many customers. We run some pretty chunky queries on this and get fast responses. We want to move the hosting elsewhere, but we simply can't get a database running anywhere near as fast.
We've tried on a new VPS server, with SSD, 12 cores and 16 gigs ram. Queries are taking 10 times longer than the shared hosting's db.
(In fact, increasing number of cores made little difference). We tried MySQL and MariaDB + our hosts also setup lightspeed, memcache and Redis, but they didn't help.
For a specific query I've been testing with, the shared server takes approx 0.7 seconds to run, the new server takes about 8 seconds.
We've checked that the indexes are in place (the EXPLAIN query looks the same on both), and we're confident that the whole query isn't cached because minor changes to the query don't effect speed. The output is tiny, so it's the processing that's slower.
The slow queries are long and wide ranging, but effectively it's when it's inefficiently trying to match rows where an ID is saved as a varchar. eg.
SELECT post.ID
FROM post
LEFT JOIN meta
ON post.ID = meta.postID
AND meta.key = "ABC"
WHERE post.type = "XYZ"
AND meta.value = "123456";
An EXPLAIN on a slow query reveals:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, _booking_all_day, ref, "post_id,meta_key", meta_key, 576, const, 71474, Using where; Using temporary; Using filesort
....
I've pasted the SHOW VARIABLES for each server here:
https://pastebin.com/iW6WL3zC - fast, shared server.
https://pastebin.com/kJGKHyqs - new, slower server.
I have a decent amount of experience with MySQL, but I'm no db engineer! This is my first time with a MariaDB so I don't know if there are important optimisations that I'm unaware of.
One obvious answer is re-write the bad SQL code. We will do, but we will need to move the hosting before we have time to do that. This question is just on why the same query could be so different / any recommendations for trying to get the new server running as fast (or even faster)! Thanks.
sql linux mariadb
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
we've got a site on cheap, shared hosting.
They've got a MariaDB server, which I presume is shared by many customers. We run some pretty chunky queries on this and get fast responses. We want to move the hosting elsewhere, but we simply can't get a database running anywhere near as fast.
We've tried on a new VPS server, with SSD, 12 cores and 16 gigs ram. Queries are taking 10 times longer than the shared hosting's db.
(In fact, increasing number of cores made little difference). We tried MySQL and MariaDB + our hosts also setup lightspeed, memcache and Redis, but they didn't help.
For a specific query I've been testing with, the shared server takes approx 0.7 seconds to run, the new server takes about 8 seconds.
We've checked that the indexes are in place (the EXPLAIN query looks the same on both), and we're confident that the whole query isn't cached because minor changes to the query don't effect speed. The output is tiny, so it's the processing that's slower.
The slow queries are long and wide ranging, but effectively it's when it's inefficiently trying to match rows where an ID is saved as a varchar. eg.
SELECT post.ID
FROM post
LEFT JOIN meta
ON post.ID = meta.postID
AND meta.key = "ABC"
WHERE post.type = "XYZ"
AND meta.value = "123456";
An EXPLAIN on a slow query reveals:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, _booking_all_day, ref, "post_id,meta_key", meta_key, 576, const, 71474, Using where; Using temporary; Using filesort
....
I've pasted the SHOW VARIABLES for each server here:
https://pastebin.com/iW6WL3zC - fast, shared server.
https://pastebin.com/kJGKHyqs - new, slower server.
I have a decent amount of experience with MySQL, but I'm no db engineer! This is my first time with a MariaDB so I don't know if there are important optimisations that I'm unaware of.
One obvious answer is re-write the bad SQL code. We will do, but we will need to move the hosting before we have time to do that. This question is just on why the same query could be so different / any recommendations for trying to get the new server running as fast (or even faster)! Thanks.
sql linux mariadb
sql linux mariadb
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
asked Nov 19 '18 at 10:40
Jamie GJamie G
85131332
85131332
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
Nov 19 '18 at 10:43
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
Nov 19 '18 at 10:45
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
Nov 19 '18 at 11:47
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
Nov 19 '18 at 13:23
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
Nov 19 '18 at 13:25
|
show 8 more comments
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
Nov 19 '18 at 10:43
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
Nov 19 '18 at 10:45
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
Nov 19 '18 at 11:47
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
Nov 19 '18 at 13:23
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
Nov 19 '18 at 13:25
1
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
Nov 19 '18 at 10:43
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
Nov 19 '18 at 10:43
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
Nov 19 '18 at 10:45
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
Nov 19 '18 at 10:45
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
Nov 19 '18 at 11:47
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
Nov 19 '18 at 11:47
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
Nov 19 '18 at 13:23
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
Nov 19 '18 at 13:23
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
Nov 19 '18 at 13:25
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
Nov 19 '18 at 13:25
|
show 8 more comments
1 Answer
1
active
oldest
votes
..3zC Old, fast: MariaDB 10.1.27 buffer_pool=18G
..yqs New, slow: MariaDB 10.1.37 buffer_pool=128M
innodb_buffer_pool_size is very important to performance. Your 10x clue is what I often see as the difference between I/O-bound and in-cache.
How much RAM in each machine? The buffer_pool should be about 70% of available RAM.
Meanwhile, do they have the same version of WP?
A tips on improving WP's postmeta performance: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
The recommended index change may actually speed up that query in the slower machine enough to match the faster machine.
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
Nov 20 '18 at 18:16
add a comment |
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%2f53372851%2fmariadb-server-running-10-x-faster-than-anything-we-can-reproduce-ideas-why%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
..3zC Old, fast: MariaDB 10.1.27 buffer_pool=18G
..yqs New, slow: MariaDB 10.1.37 buffer_pool=128M
innodb_buffer_pool_size is very important to performance. Your 10x clue is what I often see as the difference between I/O-bound and in-cache.
How much RAM in each machine? The buffer_pool should be about 70% of available RAM.
Meanwhile, do they have the same version of WP?
A tips on improving WP's postmeta performance: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
The recommended index change may actually speed up that query in the slower machine enough to match the faster machine.
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
Nov 20 '18 at 18:16
add a comment |
..3zC Old, fast: MariaDB 10.1.27 buffer_pool=18G
..yqs New, slow: MariaDB 10.1.37 buffer_pool=128M
innodb_buffer_pool_size is very important to performance. Your 10x clue is what I often see as the difference between I/O-bound and in-cache.
How much RAM in each machine? The buffer_pool should be about 70% of available RAM.
Meanwhile, do they have the same version of WP?
A tips on improving WP's postmeta performance: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
The recommended index change may actually speed up that query in the slower machine enough to match the faster machine.
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
Nov 20 '18 at 18:16
add a comment |
..3zC Old, fast: MariaDB 10.1.27 buffer_pool=18G
..yqs New, slow: MariaDB 10.1.37 buffer_pool=128M
innodb_buffer_pool_size is very important to performance. Your 10x clue is what I often see as the difference between I/O-bound and in-cache.
How much RAM in each machine? The buffer_pool should be about 70% of available RAM.
Meanwhile, do they have the same version of WP?
A tips on improving WP's postmeta performance: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
The recommended index change may actually speed up that query in the slower machine enough to match the faster machine.
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
..3zC Old, fast: MariaDB 10.1.27 buffer_pool=18G
..yqs New, slow: MariaDB 10.1.37 buffer_pool=128M
innodb_buffer_pool_size is very important to performance. Your 10x clue is what I often see as the difference between I/O-bound and in-cache.
How much RAM in each machine? The buffer_pool should be about 70% of available RAM.
Meanwhile, do they have the same version of WP?
A tips on improving WP's postmeta performance: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
The recommended index change may actually speed up that query in the slower machine enough to match the faster machine.
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
edited Nov 20 '18 at 2:15
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
answered Nov 20 '18 at 2:05
Rick JamesRick James
66.7k55899
66.7k55899
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
Nov 20 '18 at 18:16
add a comment |
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
Nov 20 '18 at 18:16
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
Nov 20 '18 at 18:01
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Set
innodb_buffer_pool_size to about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)– Rick James
Nov 20 '18 at 18:16
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Set
innodb_buffer_pool_size to about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)– Rick James
Nov 20 '18 at 18:16
add a comment |
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.
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%2f53372851%2fmariadb-server-running-10-x-faster-than-anything-we-can-reproduce-ideas-why%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
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
Nov 19 '18 at 10:43
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
Nov 19 '18 at 10:45
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
Nov 19 '18 at 11:47
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
Nov 19 '18 at 13:23
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
Nov 19 '18 at 13:25