Joomla! Issue Tracker - CMS

Download
Launch
Feeling Lucky

[#22625] - [com_actionlogs][RELEASE BLOCKER] Literally zero table indexes for performance

?
avatar PhilETaylor
PhilETaylor
15 Oct 2018

Steps to reproduce the issue

Joomla 3.9Rc1

Add a million unique rows to the action logs table.

Use the filters

Expected result

Optimised queries using indexes

Actual result

a million rows are consulted before a result can be returned in mysql

System information (as much as possible)

Mysql

Additional comments

Installed table structure:

CREATE TABLE `#__action_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `message_language_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `log_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `extension` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL DEFAULT 0,
  `item_id` int(11) NOT NULL DEFAULT 0,
  `ip_address` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0.0.0.0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4001001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;```
avatar PhilETaylor PhilETaylor - open - 15 Oct 2018
avatar joomla-cms-bot joomla-cms-bot - change - 15 Oct 2018
Labels Added: ?
avatar joomla-cms-bot joomla-cms-bot - labeled - 15 Oct 2018
avatar PhilETaylor
PhilETaylor - comment - 15 Oct 2018

Maybe at a minimum these three could be added:

ALTER TABLE `#__action_logs` ADD INDEX (`user_id`);
ALTER TABLE `#__action_logs` ADD INDEX (`user_id`, `log_date`);
ALTER TABLE `#__action_logs` ADD INDEX (`user_id`, `extension`);
ALTER TABLE `#__action_logs` ADD INDEX (`extension`, `item_id`);
avatar PhilETaylor PhilETaylor - change - 15 Oct 2018
The description was changed
avatar PhilETaylor PhilETaylor - edited - 15 Oct 2018
avatar PhilETaylor PhilETaylor - change - 15 Oct 2018
The description was changed
avatar PhilETaylor PhilETaylor - edited - 15 Oct 2018
avatar PhilETaylor
PhilETaylor - comment - 15 Oct 2018

Example with just 6000 rows in the table - just filtering by userid:

BEFORE indexes:

screenshot 2018-10-15 at 09 55 50

AFTER indexes:

screenshot 2018-10-15 at 09 55 23

avatar PhilETaylor PhilETaylor - change - 15 Oct 2018
Title
…
[com_actionlogs] Literally zero table indexes for performance
[com_actionlogs][RELEASE BLOCKER] Literally zero table indexes for performance
avatar PhilETaylor PhilETaylor - edited - 15 Oct 2018
avatar brianteeman brianteeman - change - 15 Oct 2018
Status New ⇒ Closed
Closed_Date 0000-00-00 00:00:00 ⇒ 2018-10-15 14:47:48
Closed_By ⇒ brianteeman
avatar brianteeman brianteeman - close - 15 Oct 2018

Add a Comment

Login with GitHub to post a comment