google cloud storage mySql query reads only a few hundred lines per second

Question

I have set up a database with a table of approximately 100GB. I use 4cpu and 15GB ram on the gcs. When I make a query I can see from the read/write operations dashboards that only a few hundred lines are read per second which will take forever to complete.

the query is done on DBeaver, it is not a complicated query as can be seen from the picture attached. I simply do not understand why it is so slow??

the query is simply this:

"""INSERT INTO analytics_agg ( hash, product, interface, click_time_0, click_time_5, click_time_10, click_time_30, click_time_60) SELECT hash, product, interface, count(case when click_time=0 then 1 else 0 end) , count(case when click_time=5 then 1 else 0 end) , count(case when click_time=10 then 1 else 0 end) , count(case when click_time=30 then 1 else 0 end) , count(case when click_time=60 then 1 else 0 end) FROM analytics group by hash,product,interface """

question from:https://stackoverflow.com/questions/65894917/google-cloud-storage-mysql-query-reads-only-a-few-hundred-lines-per-second

Answer 1

As you're selecting from one table and inserting in another, the selection from the first table has a large impact on the final insertion rate and performance.

Try to create the following index, which should optimize the selection query and improve the overall performance of the insertion:

ALTER TABLE `analytics` ADD INDEX `analytics_idx_hash_product_interface` (`hash`,`product`,`interface`);