Hi, Witold! On Feb 21, Witold Filipczyk wrote:
Hi, 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.
That's a very interesting idea. Basically, you're saying that if a user has no probal privileges and database privileges on only one database and no wildcards in the database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly doable. It's even not very difficult to do, I'd think. (*) this can be extended to privileges on few (more than one) databases and on wildcards in the database names, but let's start from something simple.
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.
Kind of. Privileges are stored in memory in lists and hashes. You'd need to traverse them to see what databases a user has grants for. Regards, Sergei Chief Architect MariaDB and security@mariadb.org