how to make this mysql query fast? I have millions of data inside my table [closed]

Question

I have a table called ad_view and it has 18.9 million number of data inside that table.

This is the table design of ad_vew

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

I am using this query to get the ad_id and the count of ad_id depending on the network_id, so in this case the 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 query takes forever to load. Do you have any suggestion how to make this query fast?

I am not a database expert, if it takes to redesign this table, I would do that.

Any suggestion will be greatly appreciated, thanks!

Answer 1

This will help:

ALTER TABLE ad_view ADD INDEX (network_id, ad_id).

Make sure you have tuned your innodb_buffer_pool_size to hold the frequently-requested portion of your table in memory.

You might like my presentation, How to Design Indexes, Really. That presentation doesn't go into indexing for GROUP BY, but you can think of it like a range condition.

Answer 2

1 Create an index on network_id since you're searching by it

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

2 If you're trying to get the count of ad_id for a given network_id, why do you need ad_id in your results? and why do you need to order by? I don't understand this. If all you want 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 just a number. See this demo

PS: your initial post included a broken query (in the order by clause), which you changed after I made a comment. Your current query still doesn't give you what you say you want. I just tried it on this fiddle