Remove Redundant Indexes in DB

[apmuthu]

Expected behavior and actual behavior.

Database Index optimisation - remove redundant indexes:

ALTER TABLE `activity` 
    DROP INDEX `IDX_site_id`, 
    DROP INDEX `IDX_site_created`, 
	DROP INDEX `IDX_data_item_type`, 
	DROP INDEX `IDX_activity_site_type_created_job`, 
	ADD INDEX `IDX_activity_site_type_created_job` (`site_id`, `date_created`, `data_item_type`, `entered_by`, `joborder_id`); 
ALTER TABLE `attachment` 
    DROP INDEX `IDX_data_item_id`, 
	DROP INDEX `IDX_site_file_size`, 
	DROP INDEX `IDX_type_id`, 
	ADD INDEX `IDX_type_id` (`data_item_id`, `data_item_type`); 
ALTER TABLE `candidate_joborder` 
    DROP INDEX `IDX_site_id`, 
	DROP INDEX `IDX_joborder_id`, 
	DROP INDEX `IDX_site_joborder`, 
	ADD INDEX `IDX_site_joborder` (`joborder_id`, `site_id`); 
ALTER TABLE `candidate_joborder_status_history` 
    DROP INDEX `IDX_site_id`, 
	DROP INDEX `IDX_status_to`, 
	DROP INDEX `IDX_joborder_site`; 
ALTER TABLE `saved_list_entry` 
    DROP INDEX `IDX_data_item_type`; 

Steps to reproduce the problem.

What version of opencats are you running? WAMP or LAMP?

• 0.9.4 'Countach'
• LAMP

attach appropriate error logs. Please attach [apache/mysql] error/access logs as needed.

[RussH]

@mlespiau @skrchnavy thoughts on this? I assume these indexes are okay to remove - but would 'index removal' actually assist with database optimisation at-all?

[apmuthu]

The number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes. Legitimate uses of redundant indexes are for leveraging storage engine specific features.

• Multi Column Search / Index
• Indexing - Rules of Thumb

[RussH]

@apmuthu it woudl be great if you and @amaisonneuve could discuss this and reach a conclusion. See https://groups.google.com/g/opencats-dev/c/zTgYCiXjMj0 `Russ, yes you are correct the existing indexing in OpenCATS is tragic at best.

The Query is a monster and needs a re-write, which I have yet to do but want to do.

We have some key problem areas which are causing multiple temp tables to be generated which don't have indexing.

#1 SQL_CALC_FOUND_ROWS #2 GROUP BY candidate.candidate_id #3 ORDER BY firstName ASC

I've made some small changes in the SQL, I can PR the other ones.

I've created new indexex on top of the existing ones for now:

Index (List): candidate_id, site_id, is_admin_hidden, date_modified Index (PRIMARY): candidate_id Index (Prime) candidate_id, site_id `