Many to many relation and relationCount column

Need Help
#1

I have updated OC from 2.x to 3.x

My model has many to many relation attribute <-> item
I have about 150 attributes, 160.000 items, 2.000.000 rows in attributes_items table.

If i want to count items for each attribute Number Column - October CMS - 3.x
it takes time to render list. In OC 2 it takes few seconds.

#2

Hi Mk, the easiest way is to have rainlab debugger plugin and check the query execution time.

Try to execute same query in phpmyadmin or any other mysql manager. Use “Explain” to get information how query is performing.

#3

Show us your relation and column definitions, also check new method of counting relations from v2.1: Release Note 27: October CMS 2.1 - Stable Release - October CMS

#4

Which debugger plugin do you use?

#5
2 Likes
#6 
//model Wyposazenie
class Wyposazenie extends Model{
	public $belongsToMany = [
        'pojazdy' => [
            Pojazdy::class,
            'table'      => 'mkinternet_pojazdy_wyposazenie_pojazdy',
        ],
    ];	
}

// wyposazenie/columns.yaml
    pojazdy_count:
        label: Relation model count
        type: number
        relation: pojazdy

#sql create table

CREATE TABLE `mkinternet_pojazdy_wyposazenie_pojazdy` (
  `wyposazenie_id` int(11) NOT NULL,
  `pojazdy_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `mkinternet_pojazdy_wyposazenie_pojazdy`
  ADD UNIQUE KEY `pojazdy_wyposazenie` (`pojazdy_id`,`wyposazenie_id`),
  ADD KEY `mkinternet_pojazdy_wyposazenie_pojazdy_pojazdy_id_index` (`pojazdy_id`),
  ADD KEY `mkinternet_pojazdy_wyposazenie_pojazdy_wyposazenie_id_index` (`wyposazenie_id`),
  ADD KEY `mkinternet_pojazdy_wyposazenie_pojazdy_created_at_index` (`created_at`),
  ADD KEY `mkinternet_pojazdy_wyposazenie_pojazdy_deleted_at_index` (`deleted_at`);
        relationCount: true
#7

Column definition looks ok if this line is part of it, you will have to examine SQL query plan.