thusharaK thusharaK - 6 months ago 10
SQL Question

What are the ways to improve the performance for below query

I have below table query which is executed to get the latest REG_LOG(table) update, perform full table scan to get the results.

SELECT REG_PATH,
REG_USER_ID,
REG_LOGGED_TIME,
REG_ACTION,
REG_ACTION_DATA
FROM REG_LOG
WHERE REG_LOGGED_TIME > <last-access-time>
AND REG_LOGGED_TIME < '<current-time>'
AND REG_TENANT_ID = <tenant-id>


This table can contain millions of data.
My question is what are the things we can do to increase the performance of this query? As per a workaround we have created an index for REG_LOGGED_TIME column to reduce full table scan.

Answer

Have 2 fields in WHERE clause. There is first candidates for indexing. You should analyze selectivity of you fields. It is count distinct values divided by number rows. If result number is more then 200, you must create indexes.

Example:

CREATE INDEX ON REG_LOG (REG_TENANT_ID, REG_LOGGED_TIME);

Also you should review your other queries against this table. Probably you should create just one composite index. In this case, the first field must be column with biggest selectivity.

Comments