I'm working on audio fingerprinting problem where I need to query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to 100K rows in total, using multiple queries (between 20 and 50 queries).
The table has 3 columns, a hash and two int values. No constraints whatsoever. The hash column has a lot of collisions/duplicates. Here is the output for show create table
CREATE TABLE `fingerprints` (
`hash` binary(10) NOT NULL,
`int1` mediumint(8) unsigned NOT NULL,
`int2` mediumint(8) unsigned NOT NULL,
KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
The query is simple, here is an example:
select int1 ,int2 from fingerprints
WHERE hash in
(UNHEX("1ff99335cce004f2765d"),UNHEX("14c4b93ed575982ed2e4")
,UNHEX("41044b0cf21dc8ac8f9b"),UNHEX("a791403ca116b4da53dd")
,UNHEX("d9f91514b900c25fa095"),UNHEX("3349f906deae6cd32883")
,UNHEX("221c0e3e2bc243fb0fe5") .... more here);
I've tried different hardware specs (using AWS with only one machine/instance). Different my.cnf configurations but no significant performance boost.
the target speed threshold for this operation (total queries time) is 5 sec. But the best I've got in average is 3 sec for only one single query (if I have 20 queries, the total operation time is 1 minute).
final note: when profiling the query, SHOW profile command shows that the slowest part was (SENDING DATA) state. The query becomes slower when the result set is larger (i.e retrieving 10k rows takes about 6 sec, while retrieving 1000 rows takes 2 sec)
Questions:
- What is the speed estimation for such query scenario for an SSD machine with enough RAM to hold indices. I have no experience working at this scale.
- Do you have recommendation for specific db setup? should I try mysql Memory engine? partitioning here is a necessity with distributed machines? should I switch to innodb?
my setup:
- read only myisam table compressed with myisampack and indexed on the where (hash) column.
- the index table (MYI file) is fully loaded to RAM
- SSD hard disk with limited iops (amazon AWS). According to AWS graphs i'm hitting 700 Iops sometimes.
Edit:
SHOW INDEX output:
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| fingerprints | 1 | hash | 1 | hash | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
EXPLAIN QUERY output (for the example query)
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | fingerprints | NULL | range | hash | hash | 10 | NULL | 4912 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+