In our application for each query we build a unique query id, and insert to table according to this query id. We used to use Hive for this and it was easy;
create table hivetable (
...
)PARTITIONED BY ( query_id INT ) ...
and inserting to table was like this:
alter table hivetable add partition (query_id=?);
insert into hivetable partition(query_id=?) select ...;
And now we are migrating to MySQL and I am confused. I tried below table and insert like this:
create table mytable (...) PARTITION BY HASH(query_id);
insert into mysql values(?,?,...,<query_id>);
Apperantly it didn't work in a way I wanted. It inserts the data of all query_ids in one partition. So I would really appreciate some help Thanks
At the end Indexing worked well for me. We wanted to use smth like Hive partitioning because of fastening to select, delete operations. And I found indexing in MySQL is pretty corresponds our needs. If you index the columns which you use where statements, it will fasten your queries pretty well. You can find more info in following link: https://dev.mysql.com/doc/refman/8.0/en/create-index.html