MySQL Query/Table in need of optimization

MySQL Query/Table in need of optimization

I have a query that is taking an embarrassingly long time. ~7 minutes embarrassing. I would really appreciate some help. Missing indexes? Rewrite the query? All of the above?

I have a query that is taking an embarrassingly long time. ~7 minutes embarrassing. I would really appreciate some help. Missing indexes? Rewrite the query? All of the above?

Many thanks

mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64)

The query looks like:

SELECT COUNT(*) AS count_all, name 
FROM api_events ae
INNER JOIN products p on p.token=ae.product_token 
WHERE (ae.created_at > '2019-01-21 12:16:53.853732') 
GROUP BY name

Here are the two table definitions

api_events has ~31 million records

CREATE TABLE `api_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `api_name` varchar(200) NOT NULL,
  `hostname` varchar(200) NOT NULL,
  `controller_action` varchar(2000) NOT NULL,
  `duration` decimal(12,5) NOT NULL DEFAULT '0.00000',
  `view` decimal(12,5) NOT NULL DEFAULT '0.00000',
  `db` decimal(12,5) NOT NULL DEFAULT '0.00000',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `product_token` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_token` (`product_token`)
) ENGINE=InnoDB AUTO_INCREMENT=64851218 DEFAULT CHARSET=latin1;

and products has only 12 records

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(2000) NOT NULL,
  `token` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;


Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

MySQL Database Bootcamp: Go from SQL Beginner to Expert | Simpliv

MySQL Database Bootcamp: Go from SQL Beginner to Expert

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

This SQL Full Course video will cover all the topics of SQL starting from scratch. This video is great for beginners who want to learn SQL and for advanced people to brush up their skills.

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts