How to optimize SELECT COUNT with LEFT JOIN?

Question

I have a MariaDB 10.5.10 database, with a table activity made of 150,000 rows. it holds:

• an id field (integer, primary key)
• a tenant. There are about 85,000 rows with tenant = 12
• an activity_type (integer, acting as a key to another table activity_type)

Another table, activity_type, has 6 rows, with an id (primary key, integer)

This query, which is generated from an ORB (and which I can virtually not modify) requires about 4-5 seconds to execute, and I would like to make it run faster:

select count(activity.id) from activity
   left join activity_type on activity.type = activity_type.id
where tenant = 12 ;

When I remove the left join row, it runs much faster, about 0.7 seconds.

Using explain, I can see that without left join, the extra column mentions using index(actually, the index on the tenant column):

explain select SQL_NO_CACHE count(activity.id) from activity
where tenant = 12;

But with left join the extra column is empty:

explain select SQL_NO_CACHE count(activity.id) from activity
  left join activity_type on activity.type = activity_type.id
where tenant = 12;

And with left join but NO where clause, it also shows Using index:

explain select SQL_NO_CACHE count(activity.id) from activity
  left join activity_type on activity.type = activity_type.id;

Could you please help me improving the performance when both the LEFT JOIN and the WHERE statements are present?

Please note that:

• the LEFT JOIN does not add any rows to the results, because there is only one activity_type row per activity (I have also tried to add a UNIQUE index on the activity_type.id, but it does not change anything on performance)
• however I need the LEFT JOIN statement, because in some cases, the users might want to add more criteria (in the where clause) related to the fields in the activity_type table.

Many thanks!

Answer 1

Add the following index to the activity table

alter table activity add index tp_ten(type,tenant);

See Compound (Composite) Indexes

Answer 2

I need the LEFT JOIN statement, because in some cases, the users might want to add more criteria (in the where clause) related to the fields in the activity_type table.

No you don't. Construct the desired query based on the criteria the user give you. It appears that AND activity_type = '...' is the only case where the LEFT JOIN clause is needed.

Other tips:

• Say COUNT(*) instead of COUNT(x) unless you need to check for x IS NOT NULL.
• Please provide SHOW CREATE TABLE so that we know exactly what indexes, datatypes, etc, are in the tables.
• MariaDB (but not MySQL) is smart enough to get rid of any LEFT JOIN that is not really needed.

The EXPLAINs:

1. In InnoDB, the PK is implicitly tacked onto any secondary index. This means it is "Using index" (tenent, id)
2. The empty "Extra" is probably due to thinking it needs type.
3. The PRIMARY KEY(id) 'clustered' as an "index" for the data. So, "Using index" in this case means "table scan".

Confused? I don't blame you.

I would use a short string for "types" and include it as a column in activity. That would eliminate the JOIN (unless there is something else of interest in the 'types' table). But then, you might need INDEX(tenant, type), especially for the simplified version of the original query.