How to quickly execute this mysql query? I have millions of data inside my table

Question

I have a table called ad_view and it contains 18.9 million data in this table.

This is ad_vew table ad_vew

 ad_view ------- ad_id network_id publisher_id landingpage_id advertiser_id 

I use this query to get ad_id and ad_id counter depending on network_id , so in this case network_id is 4 .

 select ad_id,count(ad_id) as strength from ad_view where network_id = 4 group by ad_id order by strength desc limit 10 

This request is forever loading. Do you have an offer to quickly make this request?

I am not a database expert, if I need to redo this table, I would.

Any suggestion would be greatly appreciated, thanks!

Answer 1

This will help:

 ALTER TABLE ad_view ADD INDEX (network_id, ad_id). 

Make sure you configure your innodb_buffer_pool_size to store the frequently requested part of your table in memory.

You may like my presentation, How to create indexes, really . This presentation is not included in the indexing for GROUP BY , but you can think of it as a range state.

Answer 2

1 Create an index on network_id as you are looking for it

 ALTER TABLE `ad_view` ADD INDEX (`network_id`); 

2 If you are trying to get the ad_id counter for the given network_id , why do you need ad_id in the results? and why do you need order by ? I do not understand this. If all you need is how many ad_ids for network_id 4, then do:

 SELECT COUNT(IF(network_id=4,1,null)) as strength from ad_view 

It will return only a number. Watch this demo

PS: your initial post included a broken request (in the order by clause) that you changed after I made a comment. Your current request still does not give you what you say you want. I just tried on this fiddle