CODEWITHSUNDEEP
mysqlperformanceindexingbigdata
mmv456
mmv456

Reputation: 43

Indexing sql query

What would be the best way to index this:

select  bus.business_id, bus.latitude, bus.longitude, 
(
    SELECT  sum(checkin_count)
        FROM  yelp.checkin
        WHERE  business_id = chk.business 
) AS checkin_count
from  yelp.business bus
order by  bus.business_id
limit  10;

I am trying to index it this way:

 ALTER TABLE yelp.business ADD INDEX latitude_longitude_count_ndex (business_id, latitude, longitude);

but it is taking very long (ie I ran this overnight and it lost connection after about 2 hours).

I am running this in MySQL.

Side question, because this got me into indexing, is there a reason that this code:

select bus.business_id, bus.latitude, bus.longitude, sum(chk.checkin_count ) as checkin_count
from yelp.business bus 
inner join yelp.checkin chk 
    on bus.business_id=chk.business_id 
 group by bus.business_id, bus.latitude, bus.longitude
 order by bus.business_id
 limit 10;

is taking so long to query? Even if I change the limit to just 1 it takes very long. I eventually stopped it after 4 minutes, but I have a feeling it shouldn't be taking that long.

Upvotes: 0

Views: 46

Answers (1)

Michael
Michael

Reputation: 5335

You should set index for columns mentioned in WHERE or JOIN statements. So remove the index you've set and add one for chk.business_id or bus.business_id or both, and see what will be faster.

Upvotes: 1

Related Questions