[Solved] How can increase the performance of the query below without using distinct keyword
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

How can increase the performance of the query below without using distinct keyword

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;

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
  3. Push Filtering Conditions Into Subqueries (modified query below): Parts of the WHERE clause can pushed from the outer query to a subquery / union clause. Applying those conditions as early as possible will allow the database to scan less data and run the query more efficiently.
Optimal indexes for this query:
ALTER TABLE `table1` ADD INDEX `table1_idx_customerip` (`customerIp`);
ALTER TABLE `table2` ADD INDEX `table2_idx_customerip` (`customerIp`);
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.