I have a very large table (around 50 GB) but with few columns:
CREATE TABLE my_table (
id BIGINT(20) NOT NULL AUTO_INCREMENT,
id_sec BIGINT(20) NULL,
full_name VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (id),
FULLTEXT INDEX fts_full_name (full_name)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
when I execute the following queries:
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ' IN BOOLEAN MODE)
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ')
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ' IN BOOLEAN MODE) LIMIT 1000
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ') LIMIT 1000
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ' IN BOOLEAN MODE) LIMIT 100
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('lastname middle first ') LIMIT 100
Sometimes send me the following error message:
SQL Error (128): Table handler out of memory
But it does not happen when I execute:
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('+lastname +middle +first ' IN BOOLEAN MODE)
SELECT * FROM my_table WHERE MATCH(full_name) AGAINST('+lastname +middle +first ')
I have tried with the following values and there is no improvement:
--key-buffer-size=20M
--tmp-table-size=2G
--thread-pool-max-threads=10
--max-connections=100
--max-heap-table-size=2G
--innodb-buffer-pool-size=4G
Does anyone know what could be happening? or how can I correct it?
My server is running in windows 10 (x64), 10.3.12-MariaDB (x64), 32 GB of RAM, core i7
Thanks.
Read about the 3 Variables below and experiment with them:
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
If you don't have success, then file a bug report at bugs.mysql.com (and post the bug number here).