search

opencats / OpenCATS

Applicant Tracking System (maintained code base)
http://www.opencats.org
Other
514 stars 243 forks source link

Remove Redundant Indexes in DB #312

Open apmuthu opened 7 years ago

apmuthu commented 7 years ago

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?

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

RussH commented 7 years ago

@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 commented 7 years ago

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.

RussH commented 2 years ago

@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 `