MariaDB – slow query with Copying to tmp table

This is my query:

SELECT 
vtiger_assets.asset_no, 
vtiger_assets.assetstatus,
vtiger_crmentity_user_field.starred 
FROM vtiger_assets  
INNER JOIN vtiger_crmentity ON vtiger_assets.assetsid = vtiger_crmentity.crmid 
LEFT JOIN vtiger_crmentity_user_field ON vtiger_assets.assetsid = vtiger_crmentity_user_field.recordid   
WHERE vtiger_crmentity.deleted=0 AND vtiger_assets.assetsid > 0 
ORDER BY vtiger_crmentity.modifiedtime DESC 
LIMIT 0, 35;

It performs about 30 sec

Explain shows possible key on vtiger_assets, key=primary, rows= 954878, extra=Using where; Using temporary; Using filesort Profiling shows the «Copying to tmp table» performs a long time

This fields vtiger_crmentity.modifiedtime, vtiger_assets.assetsid, vtiger_crmentity.crmid, vtiger_crmentity.deleted и vtiger_assets.assetsid have indexes.

If I exclude «ORDER BY vtiger_crmentity.modifiedtime DESC» the query performs quick, the explain shows possible key on vtiger_assets, key=primary, rows= 954878, extra=Using where;

If I return "order by" and exclude « LEFT JOIN vtiger_crmentity_user_field ON vtiger_assets.assetsid = vtiger_crmentity_user_field.recordid » the query also performs quick, the explain shows possible key on vtiger_assets, key=primary, rows= 70, extra=Using where;

The only problem I can see is the difference: vtiger_assets.assetsid is int(11) and vtiger_crmentity_user_field.recordid is int(19). But it seems to me, that the slow part is "order by". For some reason the index is not used with "left join". The query

select * from vtiger_crmentity vc order by modifiedtime desc limit 100

performs quick

The first query is generating by CRM, so I can’t change it in product environment.

All queries tested with big buffers like this:

SET SESSION join_buffer_size = 10*1024*1024*1024;
SET SESSION tmp_table_size = 10*1024*1024*1024;
SET SESSION max_heap_table_size = 10*1024*1024*1024;
SET SESSION sort_buffer_size= 10*1024*1024*1024;
SET SESSION read_rnd_buffer_size = 10*1024*1024;

Table definitions

CREATE TABLE `vtiger_assets` (
  `assetsid` int(11) NOT NULL,
  `asset_no` varchar(30) NOT NULL,
  `assetstatus` varchar(200) DEFAULT 'In Service',

…
  PRIMARY KEY (`assetsid`),
  UNIQUE KEY `vtiger_assets_UN` (`asset_no`),
  UNIQUE KEY `vtiger_assets_SN_UN` (`serialnumber`),
  KEY `vtiger_assets_product_IDX` (`product`) USING BTREE,
  CONSTRAINT `fk_1_vtiger_assets` FOREIGN KEY (`assetsid`) REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;


CREATE TABLE `vtiger_crmentity` (
  `crmid` int(19) NOT NULL,
  `modifiedtime` datetime NOT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',

…
  PRIMARY KEY (`crmid`),
  KEY `crmentity_smcreatorid_idx` (`smcreatorid`),
  KEY `crmentity_modifiedby_idx` (`modifiedby`),
  KEY `crmentity_deleted_idx` (`deleted`),
  KEY `crm_ownerid_del_setype_idx` (`smownerid`,`deleted`,`setype`),
  KEY `vtiger_crmentity_labelidx` (`label`),
  KEY `smownerid` (`smownerid`),
  KEY `vtiger_crmentity_modifiedtime_IDX` (`modifiedtime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;


CREATE TABLE `vtiger_crmentity_user_field` (
  `recordid` int(19) NOT NULL,
  `userid` int(19) NOT NULL,
  `starred` varchar(100) DEFAULT NULL,
  KEY `record_user_idx` (`recordid`,`userid`),
  KEY `vtiger_crmentity_user_field_recordid_IDX` (`recordid`) USING BTREE,
  CONSTRAINT `fk_vtiger_crmentity_user_field_recordid` FOREIGN KEY (`recordid`) REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Explain

MariaDB – slow query with Copying to tmp table

This is my query:

SELECT 
vtiger_assets.asset_no, 
vtiger_assets.assetstatus,
vtiger_crmentity_user_field.starred 
FROM vtiger_assets  
INNER JOIN vtiger_crmentity ON vtiger_assets.assetsid = vtiger_crmentity.crmid 
LEFT JOIN vtiger_crmentity_user_field ON vtiger_assets.assetsid = vtiger_crmentity_user_field.recordid   
WHERE vtiger_crmentity.deleted=0 AND vtiger_assets.assetsid > 0 
ORDER BY vtiger_crmentity.modifiedtime DESC 
LIMIT 0, 35;

It performs about 30 sec

Explain shows possible key on vtiger_assets, key=primary, rows= 954878, extra=Using where; Using temporary; Using filesort Profiling shows the «Copying to tmp table» performs a long time

This fields vtiger_crmentity.modifiedtime, vtiger_assets.assetsid, vtiger_crmentity.crmid, vtiger_crmentity.deleted и vtiger_assets.assetsid have indexes.

If I exclude «ORDER BY vtiger_crmentity.modifiedtime DESC» the query performs quick, the explain shows possible key on vtiger_assets, key=primary, rows= 954878, extra=Using where;

If I return "order by" and exclude « LEFT JOIN vtiger_crmentity_user_field ON vtiger_assets.assetsid = vtiger_crmentity_user_field.recordid » the query also performs quick, the explain shows possible key on vtiger_assets, key=primary, rows= 70, extra=Using where;

The only problem I can see is the difference: vtiger_assets.assetsid is int(11) and vtiger_crmentity_user_field.recordid is int(19). But it seems to me, that the slow part is "order by". For some reason the index is not used with "left join". The query

select * from vtiger_crmentity vc order by modifiedtime desc limit 100

performs quick

The first query is generating by CRM, so I can’t change it in product environment.

All queries tested with big buffers like this:

SET SESSION join_buffer_size = 10*1024*1024*1024;
SET SESSION tmp_table_size = 10*1024*1024*1024;
SET SESSION max_heap_table_size = 10*1024*1024*1024;
SET SESSION sort_buffer_size= 10*1024*1024*1024;
SET SESSION read_rnd_buffer_size = 10*1024*1024;

Table definitions

CREATE TABLE `vtiger_assets` (
  `assetsid` int(11) NOT NULL,
  `asset_no` varchar(30) NOT NULL,
  `assetstatus` varchar(200) DEFAULT 'In Service',

…
  PRIMARY KEY (`assetsid`),
  UNIQUE KEY `vtiger_assets_UN` (`asset_no`),
  UNIQUE KEY `vtiger_assets_SN_UN` (`serialnumber`),
  KEY `vtiger_assets_product_IDX` (`product`) USING BTREE,
  CONSTRAINT `fk_1_vtiger_assets` FOREIGN KEY (`assetsid`) REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;


CREATE TABLE `vtiger_crmentity` (
  `crmid` int(19) NOT NULL,
  `modifiedtime` datetime NOT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',

…
  PRIMARY KEY (`crmid`),
  KEY `crmentity_smcreatorid_idx` (`smcreatorid`),
  KEY `crmentity_modifiedby_idx` (`modifiedby`),
  KEY `crmentity_deleted_idx` (`deleted`),
  KEY `crm_ownerid_del_setype_idx` (`smownerid`,`deleted`,`setype`),
  KEY `vtiger_crmentity_labelidx` (`label`),
  KEY `smownerid` (`smownerid`),
  KEY `vtiger_crmentity_modifiedtime_IDX` (`modifiedtime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;


CREATE TABLE `vtiger_crmentity_user_field` (
  `recordid` int(19) NOT NULL,
  `userid` int(19) NOT NULL,
  `starred` varchar(100) DEFAULT NULL,
  KEY `record_user_idx` (`recordid`,`userid`),
  KEY `vtiger_crmentity_user_field_recordid_IDX` (`recordid`) USING BTREE,
  CONSTRAINT `fk_vtiger_crmentity_user_field_recordid` FOREIGN KEY (`recordid`) REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Explain

Show table status

RAM=32G
innodb_buffer_pool_size = 16G
Data in InnoDB tables: 7G (Tables: 1335)

What can I do to improve the performance of the first query?

• performance
• mariadb

• Table definitions Do not skip columns which are used anywhere in your code (indices, FKs, query..) – Akina Commented Feb 3 at 11:18
• It can either use the crmid index to optimizs the join, or the modifiedby index to optimize ORDER BY. But I don't think it's able to do both. – Barmar Commented Feb 3 at 17:58
• How much RAM do you have? What is the value of innodb_buffer_pool_size? – Rick James Commented Feb 5 at 18:47
• All information added – Юрий Глущенко Commented Feb 9 at 9:27

1 Answer 1

Add this composite index to vtiger_crmentity:

INDEX(deleted, modifiedtime)

in place of KEY crmentity_deleted_idx (deleted)

All tables should have a PRIMARY KEY. It is unusual to have 3 Unique keys (including the PK) on a table. Are you sure they are all needed?

ROW_FORMAT=COMPRESSED may cost speed while saving not much disk space. How big are the tables? (SHOW TABLE STATUS) How much RAM do you have? What is the setting of innodb_buffer_pool_size?

10G for four of those settings is unreasonable; it wastes a lot of RAM and threatens swapping.

Add this 'covering' (and composite) index to vtiger_crmentity_user_field:

INDEX(recordid,  starred)

Since the WHERE clause filters on two different tables, it is unlikely to do much toward speeding up the query.

Please provide EXPLAIN SELECT after adding those indexes. Something else may show up.

I suspect there is "swapping" -- considering those huge settings you chose.