0

I'm trying to optimize a large(4M rows) and busy table of mine. It's currently

CREATE TABLE `person_search` ( `token` CHAR(63), -- each word of the person's name `person_id` BIGINT(20) UNSIGNED, -- foreign key on person.id; not constrained because MyiSAM `birth_date` DATE NULL, -- for ordering the results `gender` ENUM('male', 'female') NULL, -- additional filtering INDEX (`token`, `birth_date`, `person_id`), INDEX (`person_id`, `token`) ) ENGINE='MyISAM';

Searches are performed by INNER JOINing this table with itself as many times as needed for the number of tokens in the user's query. (For performance, searches are always prefix searches (LIKE 'foo%'); not sub-string searches (LIKE '%foo%')).

To potentially improve performance, I tried MODIFYing the token column to be a massive ENUM():

ALTER TABLE `person_search` MODIFY `token` ENUM( 'AAB', 'AABYE', 'AACH', -- snip ~110'000 values; longest value is 20 chars 'ZYSTRA', 'ZYWYLO', 'ZYZAK' ) NOT NULL;

but after a few seconds, MySQL returned the error ERROR 1117 (HY000): Table definition is too large. Googling it didn't help.

I think MySQL/MyiSAM can't do this, and I'll have to handle the enumeration myself in the application instead of the DB, or make a person_search_token_enum table and add more inner joins.

But maybe I'm missing a setting or something. It's not a shared server; I can make any configuration changes I want.

Improve this question
1
  • Recomend () use VARCHAR () use InnoDB. (*) not use enum for so many options. Commented Mar 22, 2024 at 20:54

1 Answer 1

Reset to default
2

Read this in the manual: https://dev.mysql.com/doc/refman/8.0/en/enum.html

An ENUM column can have a maximum of 65,535 distinct elements.

This data type enumerates values using either an 8-bit integer or a 16-bit integer. That number of bits can enumerate at most 216 distinct values.

That explains the error you encountered.

It seems like your attempt to optimize is very similar to fulltext indexing. You should read that section of the manual and see if it does what you want, probably more efficiently and much easier to implement.

Improve this answer