[
https://issues.apache.org/jira/browse/OOZIE-1717?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16334430#comment-16334430
]
Attila Sasvari commented on OOZIE-1717:
---------------------------------------
patch 00 adds 6 new indices and changes *db.version* to *3.1*. Here are the
indices from the SQL file generated by the OozieDBCLI tool:
CREATE INDEX I_BNDLTNS_BUNDLE_ID ON BUNDLE_ACTIONS (bundle_id);
CREATE INDEX I_BNDLJBS_CREATED_TIME ON BUNDLE_JOBS (created_time);
{color:#333333}*CREATE INDEX I_BNDLJBS_END_TIME ON BUNDLE_JOBS
(end_time);*{color}
CREATE INDEX I_BNDLJBS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (last_modified_time);
CREATE INDEX I_BNDLJBS_STATUS ON BUNDLE_JOBS (status);
CREATE INDEX I_BNDLJBS_SUSPENDED_TIME ON BUNDLE_JOBS (suspended_time);
*CREATE INDEX I_BNDLJBS_USER_NAME ON BUNDLE_JOBS (user_name);*
CREATE INDEX I_CRD_TNS_CREATED_TIME ON COORD_ACTIONS (created_time);
CREATE INDEX I_CRD_TNS_EXTERNAL_ID ON COORD_ACTIONS (external_id);
CREATE INDEX I_CRD_TNS_JOB_ID ON COORD_ACTIONS (job_id);
CREATE INDEX I_CRD_TNS_LAST_MODIFIED_TIME ON COORD_ACTIONS (last_modified_time);
CREATE INDEX I_CRD_TNS_NOMINAL_TIME ON COORD_ACTIONS (nominal_time);
CREATE INDEX I_CRD_TNS_RERUN_TIME ON COORD_ACTIONS (rerun_time);
CREATE INDEX I_CRD_TNS_STATUS ON COORD_ACTIONS (status);
CREATE INDEX I_CRD_JBS_BUNDLE_ID ON COORD_JOBS (bundle_id);
CREATE INDEX I_CRD_JBS_CREATED_TIME ON COORD_JOBS (created_time);
*CREATE INDEX I_CRD_JBS_END_TIME ON COORD_JOBS (end_time);*
CREATE INDEX I_CRD_JBS_LAST_MODIFIED_TIME ON COORD_JOBS (last_modified_time);
CREATE INDEX I_CRD_JBS_NEXT_MATD_TIME ON COORD_JOBS (next_matd_time);
CREATE INDEX I_CRD_JBS_STATUS ON COORD_JOBS (status);
CREATE INDEX I_CRD_JBS_SUSPENDED_TIME ON COORD_JOBS (suspended_time);
*CREATE INDEX I_CRD_JBS_USER_NAME ON COORD_JOBS (user_name);*
CREATE INDEX I_SL_VNTS_DTYPE ON SLA_EVENTS (bean_type);
CREATE INDEX I_SL_RRTN_NOMINAL_TIME ON SLA_REGISTRATION (nominal_time);
CREATE INDEX I_SL_SMRY_APP_NAME ON SLA_SUMMARY (app_name);
CREATE INDEX I_SL_SMRY_EVENT_PROCESSED ON SLA_SUMMARY (event_processed);
CREATE INDEX I_SL_SMRY_LAST_MODIFIED ON SLA_SUMMARY (last_modified);
CREATE INDEX I_SL_SMRY_NOMINAL_TIME ON SLA_SUMMARY (nominal_time);
CREATE INDEX I_SL_SMRY_PARENT_ID ON SLA_SUMMARY (parent_id);
CREATE INDEX I_WF_CTNS_PENDING_AGE ON WF_ACTIONS (pending_age);
CREATE INDEX I_WF_CTNS_STATUS ON WF_ACTIONS (status);
CREATE INDEX I_WF_CTNS_WF_ID ON WF_ACTIONS (wf_id);
*CREATE INDEX I_WF_JOBS_CREATED_TIME ON WF_JOBS (created_time);*
CREATE INDEX I_WF_JOBS_END_TIME ON WF_JOBS (end_time);
CREATE INDEX I_WF_JOBS_EXTERNAL_ID ON WF_JOBS (external_id);
CREATE INDEX I_WF_JOBS_LAST_MODIFIED_TIME ON WF_JOBS (last_modified_time);
CREATE INDEX I_WF_JOBS_PARENT_ID ON WF_JOBS (parent_id);
CREATE INDEX I_WF_JOBS_STATUS ON WF_JOBS (status);
*CREATE INDEX I_WF_JOBS_USER_NAME ON WF_JOBS (user_name);*
Do we want to add anything else?
I am going to run some more tests using Hue / Oozie Web UI (using MariaDB with
slow query logging enabled).
> Index user_name column
> ----------------------
>
> Key: OOZIE-1717
> URL: https://issues.apache.org/jira/browse/OOZIE-1717
> Project: Oozie
> Issue Type: Bug
> Reporter: Purshotam Shah
> Assignee: Attila Sasvari
> Priority: Minor
> Attachments: OOZIE-1717-00.patch
>
>
> User_name is one of the frequently used filter. Adding index should avoid
> full db scan.
> Index need to be done for BUNDLE_JOBS,COORD_JOBS and WF_JOBS.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
