MySQL COUNT in subquery very slow

Question

There seem to be already a lot of questions/answers to what I'm experiencing, but I couldn't adapt anything I discovered in order to fix my problem.

A have a courses table, and another views table.

The views table is meant to track page views of all the site's modules, for example:

id	table	id_row	ip	created_at
1	courses	57	xx	timestamp
2	blog	12	xx	timestamp

In our CMS, we are trying to get all the courses to a table, while getting their viewcount, but that viewcount is taking too long to render, as there are hundreds of courses and thousands of views.

Here's a simplified version of my query:

SELECT c.*,
(
    SELECT COUNT(*) FROM t_applications AS a
    WHERE a.id_course = c.id
    GROUP BY a.id_course
) AS applications,
(
    SELECT COUNT(*)
    FROM t_views AS v
    WHERE v.table = 'courses'
    AND v.id_row = c.id
    GROUP BY v.id_row
) as views
FROM t_courses AS c;

I've tried switching it up with JOIN and other techniques, but nothing seems to work.

Would appreciate some insight on how to improve this query's performance, as it's taking over a minute to render.

EDIT:

As per requested by some people, here are my SHOW CREATE TABLE of all 3 tables, again, simplified.

CREATE TABLE `t_courses`
(
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    -- other data
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=516 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

CREATE TABLE `t_views` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `table` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `id_row` bigint(20) unsigned NOT NULL,
    `ip` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=47032 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

CREATE TABLE `t_applications` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `id_course` bigint(20) unsigned NOT NULL,
    -- other data
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4177 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

Answer 1

Running a subquery in a SELECT clause is often very slow (I'm not sure but I think it runs the subquery for each row of the main query).

Using a JOIN should be more efficient as there would be a single query:

SELECT c.*,
    COUNT(DISTINCT a.id_course) AS applications,
    COUNT(DISTINCT v.id_row) as views
FROM t_courses AS c
    LEFT JOIN t_applications AS a ON c.id = a.id_course
    LEFT JOIN t_views AS v ON c.id = v.id_row
WHERE v.table = 'courses'
GROUP BY c.id;

The use of DISTINCT in the COUNT replaces your GROUP BY in the subqueries and will avoid to count the same t_applications/t_views rows multiple time.
The use of LEFT JOIN (instead of INNER JOIN) ensures that you get every rows from t_courses even if there's no matching row in the joined tables.
The ending GROUP BY is needed as a join will duplicate your t_courses rows (combination of rows between joined tables).

---

It appears that your tables use MyISAM which does not support foreign keys which means you have none. Unless you really need MyISAM, convert your tables to InnoDB (this question might help) to make your relations validated and indexed.

Then add your foreign key constraints:

ALTER TABLE t_applications
ADD CONSTRAINT fk_applications_courses
    FOREIGN KEY (id_course)
    REFERENCES t_courses(id);

ALTER TABLE t_views
ADD CONSTRAINT fk_views_courses
    FOREIGN KEY (id_row)
    REFERENCES t_courses(id);

The original answer should be definitely faster then. If you still need performance boost, manually create indexes on your columns:

CREATE INDEX idx_applications_courses ON t_applications(id_course);
CREATE INDEX idx_views_courses ON t_views(id_row);

Answer 2

Indexes:

a:  INDEX(id_course)  -- or is it the `PRIMARY KEY`?
v:  INDEX(table, id_row)  --  (in either order)

For further discussion, please provide SHOW CREATE TABLE; we need to see the indexes, datatypes, engine, etc.