Missing indices for tx_nsnewscomments_domain_model_comment

[bigahuna]

Hi there,

we have a site with very high traffic and after some profiling we found that there are lots of queries related to fields without indices. This results in very poor performance. If we add this indices to the table, the performance for the queries increases in the range of 200% to 400%. For a site with tens of thousends of vitis, adding the indices lowered the server load from 60 to 1

The fields with the missing indices are comment, deleted, hidden, t3ver_state, starttime, endtime

Here is an example query

SELECT `tx_nsnewscomments_domain_model_comment`.* 
FROM `tx_nsnewscomments_domain_model_comment` `tx_nsnewscomments_domain_model_comment` 

WHERE (`tx_nsnewscomments_domain_model_comment`.`comment` = 12785) 
AND (`tx_nsnewscomments_domain_model_comment`.`t3ver_oid` = 0) 
AND (
        (`tx_nsnewscomments_domain_model_comment`.`deleted` = 0) 
        AND (`tx_nsnewscomments_domain_model_comment`.`t3ver_state` <= 0) 
        AND (`tx_nsnewscomments_domain_model_comment`.`t3ver_wsid` = 0) 
        AND (`tx_nsnewscomments_domain_model_comment`.`t3ver_oid` = 0) AND (`tx_nsnewscomments_domain_model_comment`.`hidden` = 0) 
        AND (`tx_nsnewscomments_domain_model_comment`.`starttime` <= 1721643480) 
        AND (
            (`tx_nsnewscomments_domain_model_comment`.`endtime` = 0) 
            OR (`tx_nsnewscomments_domain_model_comment`.`endtime` > 1721643480)
        )
)

Would it be possible to add indices to the field in the ext_tables.sql?

Regards
Mike

[nitsan-technologies]

Hi Mike (@bigahuna),

Thank you for your valuable feedback.

We appreciate you bringing this to our attention. Based on your insights, we have implemented the missing indices in the relevant fields within ext_tables.sql to improve performance.

We have just released these improvements in our latest updates: Version 12.2.3 for TYPO3 V11 & V12, and Version 5.2.1 for earlier TYPO3 versions.

Thank you for helping us improve our product. Your input is invaluable, and we look forward to any further suggestions you might have.

Best regards,
Team NITSAN

[bigahuna]

Thank you very much, but wouldn't it make mor sense to add a single index for each column? As far as I understand, you added an index that only works if all columns are used in one clause (composite or multi-column index).

Instead I think it would be better to add individual indices for each column. Something like that:

CREATE INDEX idx_comment ON tx_nsnewscomments_domain_model_comment (comment);
CREATE INDEX idx_deleted ON tx_nsnewscomments_domain_model_comment (deleted);
CREATE INDEX idx_hidden ON tx_nsnewscomments_domain_model_comment (hidden);
CREATE INDEX idx_t3ver_state ON tx_nsnewscomments_domain_model_comment (t3ver_state);
CREATE INDEX idx_starttime ON tx_nsnewscomments_domain_model_comment (starttime);
CREATE INDEX idx_endtime ON tx_nsnewscomments_domain_model_comment (endtime);