Re: use index on a view

[Rick James]

Hmmmm... This will take some thinking. But first some notes...

key_buffer_size wraps at 4GB, so you really have only 1GB. But if you are only using InnoDB, recommend you set it to something smaller, like 50M.

innodb_buffer_pool_size may as well be even bigger, say 25G. But then, you say it seems to be running out of ram with 15+5 in caches.

SHOW VARIABLES LIKE '%size';

You have 20 tables, each with 60 indexes? What if a user filters on three of the columns? You won't have a decent index to work with.

What is the max size for MyISAM? A way to find out:
CREATE TABLE foo (a varchar(99)) ENGINE=MyISAM;
Then do
SHOW TABLE STATUS LIKE 'foo'\G
It will show maximum table size. This is important because it is also the limit on certain queries that need to generate temp tables in the background. The default max for MyISAM might be 4G, and you might hit it. How many bytes do you expect to get back from
select * from vw_all_tables use index (col_name) where col_name > 1000;
?

"OS to down" -- Any clues in mysqld.err? In /var/...? Elsewhere? Can you tell if you ran out of ram (seems very unlikely)?

What determines what goes into tbl01 vs tbl20? Each table has same columns and same indexes? Sounds like Partitioning.

Why 20 tables?

Are all 60 columns INTs?

How much disk space is used by the db? I would guess 400GB.

I assume the index "col_name" is an index on just col_name (plus, implicitly, the PRIMARY KEY).