maria-developers team mailing list archive
Mailing list archive
INFORMATION_SCHEMA.SCHEMATA and INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
I'm newbie, I have problem with INFORMATION_SCHEMA.
There is mariadb-10.0 with around 100000 databases. Disks are slow,
but most users have one database,
a few have more than one, but it is not a big number. Queries like
SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
bring to knees whole server.
The idea to resolve it is:
in find_files(...) instead of the for loop execute query:
SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT';
There are databases with names with _ in them and \_ in GRANTS.
This is theory, but I don't know how to do it. That's mean how to freeze the current query, how to run new query,
read results, and fill files in the find_files function and resume the current query.
Could you help?
Or do you have ideas how to solve it?