I have 2 table to record data. For example, İf you logged in, your informations are recorded into table1. When you logged in second time, your informations are recorded into table 1 and your previous enterance information shifts into table2. Tables have too much rows. To conclude, I want to get the number of different users have logged in with the given ip address for the last 60 days. Ip is the input for sql statement. However, I have performance issue in my sql query. (Two table have same structure and please ignore indexing for your suggestions) Also, is the any way to get totCount using without distinct and another select keyword. Thanks
Here is my sql:
select distinct count(*) over() as totCount into p_differentCustomerCount from ( select t1.customerNo,t1.customerIp,t1.lastlogindate from table1 t1 union all select t2.customerNo,t2.customerIp,t2.lastlogindate from table2 t2 )t where t.lastlogindate > sysdate - 60 and t.customerIp= "IP_As_Input" group by customerNo;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `table1` ADD INDEX `table1_idx_customerip` (`customerIp`); ALTER TABLE `table2` ADD INDEX `table2_idx_customerip` (`customerIp`);
SELECT DISTINCT count(*) OVER () AS totCount INTO p_differentCustomerCount FROM (SELECT t1.customerNo, t1.customerIp, t1.lastlogindate FROM table1 t1 WHERE ( t1.lastlogindate > sysdate - 60 ) AND ( t1.customerIp = 'IP_As_Input' ) UNION ALL SELECT t2.customerNo, t2.customerIp, t2.lastlogindate FROM table2 t2 WHERE ( t2.lastlogindate > sysdate - 60 ) AND ( t2.customerIp = 'IP_As_Input' ) ) t WHERE 1 = 1 AND 1 = 1 GROUP BY t.customerNo ORDER BY NULL