MySQL Query Taking Long Time To Execute

Question

I have written a query which joins three different tables. Rough descriptions of the tables are as below:

Table 1: account_data
columns: id(pk), property_id(fk), account_id(fk), value, status
No Of Data: around 30 milions

Table 2: account
columns: id(pk), service_id(fk), status
No Of Data: around 5 milions

Table 3: property
columns: id(pk), create_analytics(index), status, uri
No Of Data: few hundreds

The query is as below:

SELECT ad.value, p.uri, count(ad.id)
FROM account_data ad 
     INNER JOIN property p ON ad.property_id = p.id AND (p.status = 1) 
     INNER JOIN account ac ON ad.account_id = ac.id AND (ac.status = 1) 
     WHERE (p.create_analytics = '1' AND ac.service_id = ?) AND (ad.status = 1) 
GROUP BY ad.property_id, ad.value

At the beginning the query was taking too long. after creating index on 'create_analytics' column, it came down noticeably.

But still, the query is taking too long(more than 3 minutes). I tried by creating index on group by columns(property_id and value), creating indexes on 'status' columns, but neither didn't show up as improvement.

Just wondering, is there any other way to rewrite this query to make it faster? Or did I miss anything where creating index, change order would help?

Looking forward to all of your thoughts/suggestions to solve this issue. Thanks in advance.

Solution 1

Drop the indexes you currently have (except the PKs) and instead try adding (compound) indexes on the following:

account_data (property_id,status)

property (status,create_analytics)

account (status,service_id)

Solution 2

Also, if your current create_analytics is currently a VARCHAR , try to change it to CHAR or, better yet, if you have a limited variety of strings in that field, change it to ENUM.

Varchars are flexible and save you space on unused characters, but MySQL has to work See if there are other fields that you can make fixed so MySql can tell beforehand the width of a row.

Make sure all your joins are of the same type, I guess you ids are INT, make sure you haven't mixed BIGINTs somewhere.

Finally, try moving creating_analytics to the JOIN part, so you give a smaller resultset to the WHERE clause.

Comments

[Jens]

Try to use explain to find out where an index is missing.

[Rana]

Actually most of the pks are BIGINT , only except 'property' , which has INT type. Should that be a big issue?

[Rana]

Thanks. I tried with your suggestion and seems they improved the performance by 10-15 seconds. Just wondering why you didn't mention about 'value' column. Is there any specific reason why I shouldn't include that to index?

[ffflabs]

If property.id is INT, make FK property_id an INT too.

[ffflabs]

He may add the compound indexes, right, but he shoulnd't drop the FKs. You can't enforce structural integrity without them, and status doesn't have the same meaning across tables to try a compound FK.

[ffflabs]

Now that I consider Strawberry's idea... do you absolutely need foreign keys? Because if you don't you can change the storage to MyIsam and partition the tables by their current FK.

[Rana]

Actually I will have to keep the foreign keys because I am using ORM which requires that to maintain relationship. Thus have to maintain in innodb with fk by default. I will try with tweaking on the joins, lets see. Thanks for your valuable suggestions!

[ffflabs]

Ok, let's pretend I never suggested that. It was silly anyway

[Strawberry]

@Rana value has no relationship to any other table, and is not used in any range analysis, so I can't see any merit in indexing it (but hey, I'm no expert when it comes to optimization)

[Rana]

I was thinking it was part of group by, thus could be helpful. But didn't get much improvement with that neither.