hash - Partition tables in MySQL -

we have mysql table (table_ha) one:

name = table_ha +----------+------------------+ | hash_loc |     hash_val     | +----------+------------------+ |  242342  | 9606075000001005 | +----------+------------------+ |  431231  | 9606075000005208 | +----------+------------------+ |  342344  | 7645345456745536 | +----------+------------------+ |  324254  | 7656453453465788 | +----------+------------------+ |  656456  | 9788674534546766 | +----------+------------------+ |  674453  | 3458752778456834 | +----------+------------------+ |   ...    |       ...        | +----------+------------------+ |  765874  | 8796634586346785 | +----------+------------------+ |  864534  | 9834667054534588 | +----------+------------------+ 

we continuously execute queries following one:

select * table_ha (select 1 hash_loc union select 28700 union select 28728 ... union select 28680 union select 28694) t1 on table_ha.hash_loc = t1.hash_loc' 

we must assume might have thousands of numbers in query (enclosed in union select x). when number of rows in table_ha not high, works ok. now, imagine having thousands of millions of rows. becomes slow.

do know if partitioning can work in case this? how can applied present table? other alternative this?

note: hashloc bigint(32) , hash_val bigint(64)

i don't think partitioning needed in case. recommend making sure have index on table_ha.hash_loc.

i'm not sure why use subquery union instead of using in() predicate:

select * table_ha  hash_loc in (1, 28700, 28728 ... 28680, 28694); 

by way, bigint(32) same bigint(64). see answer types in mysql: bigint(20) vs int(20)

---

re comment:

partitioning helps when you're searching on column(s) used partitioning key. , can partition given table 1 way. indexes more useful, because can create multiple indexes per table.

i work tables of hundreds of millions of rows, , indexes lot. indexes have designed match each specific query want optimize.

you might presentation how design indexes, really. there's video of me presenting it: https://www.youtube.com/watch?v=elr7-rdu9xu