CODEWITHSUNDEEP
mysqlsqldatabasequery-optimizationinner-join
Rana
Rana

Reputation: 6154

MySQL Query Taking Long Time To Execute

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.

Upvotes: 2

Views: 16167

Answers (3)

Strawberry
Strawberry

Reputation: 33935

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)

Upvotes: 1

ffflabs
ffflabs

Reputation: 17481

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.

Upvotes: 1

r32
r32

Reputation: 1

A lot depends on how often you expect the data to change and other constraints that you are working against but you could try using a materialized view along with an index on the view itself. Note, you can configure the refresh strategy on a materialized view in MySQL.

From http://www.fromdual.com/mysql-materialized-views;

Refreshing materialized views

Materialized Views can be refreshed in different kinds. They can be refreshed:

  1. never (only once in the beginning, for static data only)
  2. on demand (for example once a day, for example after nightly load)
  3. immediately (after each statement)

A refresh can be done in the following ways:

  1. completely (slow, full from scratch)
  2. deferred (fast, by a log table)

By storing the change information in a log table. Also some snapshots or time delayed states can be produced:

  1. refresh up to date
  2. refresh full

Upvotes: 0

Related Questions