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 secur...@mariadb.org _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
