linux

The INFORMATION_SCHEMA tables are essentially hard-coded views. The data is generated programmatically via an interface that (to my understanding) only allows the whole table contents to be returned. There is a parameter that might allow some filtering, but that one is not used in INFORMATION_SCHEMA.INNODB_%, which I am familiar with. I am not aware of anything that would allow to push down any ranges. To my understanding, INFORMATION_SCHEMA.TABLES is returning the same information that you would get with SHOW TABLES, via the same internal interface (invoking virtual member functions of the handler class to get some data from the storage engines).

Regarding AUTO_INCREMENT, you should be aware that there are 2 or 3 different values, as described in MDEV-13094.

Have you considered CREATE SEQUENCE (MDEV-10139)? That is a cleaner design and based on an SQL standard. AUTO_INCREMENT is always tied to a single table, more or less.

This statement

SELECT AUTO_INCREMENT - 1 FROM INFORMATION_SCHEMA.TABLES where table_schema ='x' and table_name='y'

should only open one table `x`.`y` and not touch anything else, there is no full table scan there,

The optimizer says that "select max(id) from table" is "optimized away" when "id" is the primary key. But, strange enough,
"select rand()* max(id) from table" is not optimized away, it uses an index and it is 10 times slower.
The optimizer should be smarter. I need need to do in three steps what should take only one and be optimized:
"select field from table where id=floor(rand()*max(id))"
Maybe we could design a SQL extension for this generic case, when we need a random row from a table with a unique index on that field. It has to be very fast.
So far, it is very slow unless done in three steps
select max(id) from table into var1
set var2=floor(rand()*var1)
select field from table where id=var2

you can do in two steps, like

set var=(select max(id) from table)*rand();

select * from table where id=var;

To do it in one step you can try creating a stored function like

create function deterministic_rand() returns double deterministic return rand();

And may be it'll help, if used like

select * from table where id=deterministic_rand()*(select max(id) from table);