Bug #67279

sys_categories table columns should become indexes

Added by Gregor Agnes about 7 years ago. Updated about 7 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Categorization API
Target version:
Start date:
2015-06-03
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
5.6
Tags:
Complexity:
no-brainer
Is Regression:
No
Sprint Focus:

Description

On installations with more than 2.500 mm-relations in sys_category_record_mm the performance is getting very low.

Adding MySQL indexes to uid_local and uid_foreign fixes that.

#1

Updated by Stephan Großberndt about 7 years ago

  • Status changed from New to Needs Feedback

There are indexes present:

master has

CREATE TABLE sys_category_record_mm (
    uid_local int(11) DEFAULT '0' NOT NULL,
    uid_foreign int(11) DEFAULT '0' NOT NULL,
    tablenames varchar(255) DEFAULT '' NOT NULL,
    fieldname varchar(255) DEFAULT '' NOT NULL,
    sorting int(11) DEFAULT '0' NOT NULL,
    sorting_foreign int(11) DEFAULT '0' NOT NULL,

    KEY uid_local_foreign (uid_local,uid_foreign),
    KEY uid_foreign_tablefield (uid_foreign,tablenames(40),fieldname(3),sorting_foreign)
);

6.2 has

CREATE TABLE sys_category_record_mm (
    uid_local int(11) DEFAULT '0' NOT NULL,
    uid_foreign int(11) DEFAULT '0' NOT NULL,
    tablenames varchar(255) DEFAULT '' NOT NULL,
    fieldname varchar(255) DEFAULT '' NOT NULL,
    sorting int(11) DEFAULT '0' NOT NULL,
    sorting_foreign int(11) DEFAULT '0' NOT NULL,

    KEY uid_local_foreign (uid_local,uid_foreign),
    KEY uid_foreign_tablenames (uid_foreign,tablenames)
);

Which queries are slow? Please post examples.

#2

Updated by Gregor Agnes about 7 years ago

Sorry, my fault, you are right. I don't know why, but somewhere those indexes got lost in my installation. Did a compare and they are there.

Thx for your help. Please close.

Gregor

#3

Updated by Riccardo De Contardi about 7 years ago

  • Status changed from Needs Feedback to Closed

closed on request of the reporter.

Also available in: Atom PDF