Why do information_schema queries scan all databases? Announcing the arrival of Valued...
Check which numbers satisfy the condition [A*B*C = A! + B! + C!]
Extract all GPU name, model and GPU ram
How do pianists reach extremely loud dynamics?
Seeking colloquialism for “just because”
Book where humans were engineered with genes from animal species to survive hostile planets
At the end of Thor: Ragnarok why don't the Asgardians turn and head for the Bifrost as per their original plan?
Should I discuss the type of campaign with my players?
2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?
What is a non-alternating simple group with big order, but relatively few conjugacy classes?
Sci-Fi book where patients in a coma ward all live in a subconscious world linked together
Why was the term "discrete" used in discrete logarithm?
Storing hydrofluoric acid before the invention of plastics
Echoing a tail command produces unexpected output?
Why didn't this character "real die" when they blew their stack out in Altered Carbon?
Why is my conclusion inconsistent with the van't Hoff equation?
In predicate logic, does existential quantification (∃) include universal quantification (∀), i.e. can 'some' imply 'all'?
Why is "Consequences inflicted." not a sentence?
How to react to hostile behavior from a senior developer?
Do I really need recursive chmod to restrict access to a folder?
Generate an RGB colour grid
How widely used is the term Treppenwitz? Is it something that most Germans know?
Why did the IBM 650 use bi-quinary?
Using audio cues to encourage good posture
Output the ŋarâþ crîþ alphabet song without using (m)any letters
Why do information_schema queries scan all databases?
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)How is INFORMATION_SCHEMA implemented in MySQL?How to relate to information_schemaHow can I make a copy of a table from information_schema?Which databases are backed up by “mysqldump --all-databases”?Query INFORMATION_SCHEMA for list of indexesQuery other database's INFORMATION_SCHEMA tables1017 (HY000): Can't find file: './schema/table.MAI' (errno: 2 “No such file or directory”) [mariadb aria]error codes in information_schemaInformation_schema appearing in slow_logInformation_Schema on oracle?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I'm building a PHP application that looks up the last modified date of all databases like this:
$sql = "SELECT MAX(update_time) FROM information_schema.tables WHERE TABLE_SCHEMA LIKE '" . $row["Name"] . "'";
I have about 300 databases and this loop takes about 7 seconds, so I want to speed this up. I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow. Is there any way to fix this? I've upgraded to the latest MariaDB 10.3.14. This was a bug fixed in MySQL 8.0 so maybe MariaDB hasn't merged that yet? https://bugs.mysql.com/bug.php?id=81347
MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tablesG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tables
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)
mariadb information-schema
asked 5 mins ago
Elliott BElliott B
1085
add a comment |
I'm building a PHP application that looks up the last modified date of all databases like this:
$sql = "SELECT MAX(update_time) FROM information_schema.tables WHERE TABLE_SCHEMA LIKE '" . $row["Name"] . "'";
I have about 300 databases and this loop takes about 7 seconds, so I want to speed this up. I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow. Is there any way to fix this? I've upgraded to the latest MariaDB 10.3.14. This was a bug fixed in MySQL 8.0 so maybe MariaDB hasn't merged that yet? https://bugs.mysql.com/bug.php?id=81347
MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tablesG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tables
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)
mariadb information-schema
asked 5 mins ago
Elliott BElliott B
1085
add a comment |
I'm building a PHP application that looks up the last modified date of all databases like this:
$sql = "SELECT MAX(update_time) FROM information_schema.tables WHERE TABLE_SCHEMA LIKE '" . $row["Name"] . "'";
I have about 300 databases and this loop takes about 7 seconds, so I want to speed this up. I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow. Is there any way to fix this? I've upgraded to the latest MariaDB 10.3.14. This was a bug fixed in MySQL 8.0 so maybe MariaDB hasn't merged that yet? https://bugs.mysql.com/bug.php?id=81347
MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tablesG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tables
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)
mariadb information-schema
asked 5 mins ago
Elliott BElliott B
1085
I'm building a PHP application that looks up the last modified date of all databases like this:
$sql = "SELECT MAX(update_time) FROM information_schema.tables WHERE TABLE_SCHEMA LIKE '" . $row["Name"] . "'";
I have about 300 databases and this loop takes about 7 seconds, so I want to speed this up. I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow. Is there any way to fix this? I've upgraded to the latest MariaDB 10.3.14. This was a bug fixed in MySQL 8.0 so maybe MariaDB hasn't merged that yet? https://bugs.mysql.com/bug.php?id=81347
MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tablesG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tables
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)
mariadb information-schema
mariadb information-schema
asked 5 mins ago
Elliott BElliott B
1085
asked 5 mins ago
Elliott BElliott B
1085
asked 5 mins ago
Elliott BElliott B
1085
asked 5 mins ago
Elliott BElliott B
1085
asked 5 mins ago
Elliott BElliott B
1085
1085
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f234993%2fwhy-do-information-schema-queries-scan-all-databases%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 Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f234993%2fwhy-do-information-schema-queries-scan-all-databases%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
