Fixing typo :-) 1) A query with the DATE_ADD function d hit doesn't the index of TIME_CREATED. ==> 1) A query with the DATE_ADD function doesn't hit the index of TIME_CREATED and does a full table scan.
On Mon, May 21, 2018 at 8:43 PM Rushmin Fernando <[email protected]> wrote: > Sorry if I didn't explain it correctly. > > It is not the calculation itself. Because the calculation based on fields > of the table the DB is not able to use the indexes for the search. > > Please see a simplified test below. > > 1) A query with the DATE_ADD function d hit doesn't the index of > TIME_CREATED. > > mysql> EXPLAIN DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE > TOKEN_STATE='ACTIVE' AND ("2018-05-21 06:48:15" > DATE_ADD(TIME_CREATED, > INTERVAL 10 DAY)); > > +----+-------------+-------------------------+------+-----------------------------------+------+---------+------+------+-------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | *rows* | Extra | > > +----+-------------+-------------------------+------+-----------------------------------+------+---------+------+------+-------------+ > | 1 | SIMPLE | IDN_OAUTH2_ACCESS_TOKEN | ALL | > IDX_IDN_OAUTH2_ACCESS_TOKEN_STATE | NULL | NULL | NULL | *4276* | > Using where | > > +----+-------------+-------------------------+------+-----------------------------------+------+---------+------+------+-------------+ > > 2) A query without the function call function hits the index of > TIME_CREATED. > > mysql> EXPLAIN DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE > TOKEN_STATE='ACTIVE' AND ("2018-05-21 06:48:15" > TIME_CREATED); > > +----+-------------+-------------------------+-------+------------------------------------------+--------+---------+-------+------+-------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | *rows* | Extra | > > +----+-------------+-------------------------+-------+------------------------------------------+--------+---------+-------+------+-------------+ > | 1 | SIMPLE | IDN_OAUTH2_ACCESS_TOKEN | range | > IDX_TC,IDX_IDN_OAUTH2_ACCESS_TOKEN_STATE | IDX_TC | 4 | const | > *1* | Using where | > > +----+-------------+-------------------------+-------+------------------------------------------+--------+---------+-------+------+-------------+ > > So this clearly has an impact on the queries when the token table grows. > > Doing the calculation from the application (or even from SQL insert) > during the token generation adds only a fraction of overhead compared to > the index miss. > > Thank you > > On Mon, May 21, 2018 at 8:32 PM Sagara Gunathunga <[email protected]> wrote: > >> >> >> On Mon, May 21, 2018 at 8:10 PM, Tharindu Edirisinghe <[email protected] >> > wrote: >> >>> Currently in the IDN_OAUTH2_ACCESS_TOKEN table, we store the time of >>> token generation in TIME_CREATED column in human readable date time format. >>> Therefore at the time of token generation, we'll have to get the current >>> timestamp and store it in another column and also add the VALIDITY_PERIOD >>> to that and store in another column, which would be the expiry timestamp. >>> >>> If we are doing above, the performance of the responses sent by the >>> Token Introspection endpoint can also be improved. It sends the iat and exp >>> values doing the calculation. >>> >>> {"scope":"openid","active":true,"token_type":"Bearer","exp":1526916282,"iat":1526912682 >>> ....... >>> >>> This way there won't be a calculation in the Token Introspection >>> responses, where it can directly send the values stored in the data table. >>> >>> >>> On Mon, May 21, 2018 at 7:46 PM Rushmin Fernando <[email protected]> >>> wrote: >>> >>>> Hi Devs, >>>> >>>> Right now it is the validity period which is getting stored along with >>>> the tokens. >>>> >>>> So in order to expire a token, a calculation is needed during expiry >>>> tasks. >>>> >>>> When there a lot of tokens, this becomes a bottleneck since the >>>> database indexes are not applicable. >>>> >>> >> Do you have evidence about the latency introduced by this calculation ? >> >> According to my understanding ... >> >> - Token generation is a runtime task. >> - Token expire task is a non-runtime task and we perform above >> calculation while running this task. >> - What you suggest here is, move the calculation into token generation >> time, which will increase the runtime overhead. >> >> If my understanding on above is correct I'm not much keen with the >> suggested approach and IMO we are not solve any problems by moving >> non-runtime calculation into the runtime, it's just move the problem into a >> much critical layer. >> >> Thanks ! >> >>> >>>> But if the expiry timestamps are stored in the database, during the >>>> expiry tasks calculations are not needed and therefore the indexes are >>>> applicable. This should significantly improve the task execution time. >>>> >>>> -- >>>> *Best Regards* >>>> >>>> *Rushmin Fernando* >>>> *Technical Lead* >>>> >>>> WSO2 Inc. <http://wso2.com/> - Lean . Enterprise . Middleware >>>> >>>> mobile : +94775615183 >>>> >>>> >>>> >>> >>> -- >>> >>> Tharindu Edirisinghe >>> Associate Technical Lead | WSO2 Inc >>> Platform Security Team >>> Blog : http://tharindue.blogspot.com >>> mobile : +94 775181586 >>> >> >> >> >> -- >> Sagara Gunathunga >> >> Director; WSO2, Inc.; http://wso2.com >> Linkedin; http://www.linkedin.com/in/ssagara >> Blog ; http://ssagara.blogspot.com >> Mobile : +9471 <+94%2071%20565%209887>2149951 >> >> > > -- > *Best Regards* > > *Rushmin Fernando* > *Technical Lead* > > WSO2 Inc. <http://wso2.com/> - Lean . Enterprise . Middleware > > mobile : +94775615183 > > > -- *Best Regards* *Rushmin Fernando* *Technical Lead* WSO2 Inc. <http://wso2.com/> - Lean . Enterprise . Middleware mobile : +94775615183
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
