CODEWITHSUNDEEP
mysqloptimization
Rajneesh Kumar Gobin
Rajneesh Kumar Gobin

Reputation: 508

Indexing this sql query for best optimisation

In an existing project, I have a db in phpmyadmin, the database is supposed to hold a large amount of data in the long run. i will frequently need to draw yearly reports for customers, when i draw a report with the total record in database was around 950,000 the search time was around 15 seconds which was quite satisfying so far, however when i inserted 400,000 more test data in the database the search time jumped from 15 sec to a frustrating time of 2.08 mins. I tried to run defragment and optimise in operation without any diference in the time.

I noticed the table is not indexed ( as it should have been according to some Google searches ) i would like an advice on how to best index the tables as I am new to indexing.

Here is the query.

            $currentuserid = $this->session->userdata('userid');
$year='2008';
            $query = $this->db->query("     
select  customers.id,customers.customername,
      sum(if( MONTH(keyslog.timestamp) = 1,  keyslog.count, 0 )) as January,
      sum(if( MONTH(keyslog.timestamp) = 2,  keyslog.count, 0 )) as February,
      sum(if( MONTH(keyslog.timestamp) = 3,  keyslog.count, 0 )) as March,
      sum(if( MONTH(keyslog.timestamp) = 4,  keyslog.count, 0 )) as April,
      sum(if( MONTH(keyslog.timestamp) = 5,  keyslog.count, 0 )) as May,
      sum(if( MONTH(keyslog.timestamp) = 6,  keyslog.count, 0 )) as June,
      sum(if( MONTH(keyslog.timestamp) = 7,  keyslog.count, 0 )) as July,
      sum(if( MONTH(keyslog.timestamp) = 8,  keyslog.count, 0 )) as August,
      sum(if( MONTH(keyslog.timestamp) = 9,  keyslog.count, 0 )) as September,
      sum(if( MONTH(keyslog.timestamp) = 10, keyslog.count, 0 )) as October,
      sum(if( MONTH(keyslog.timestamp) = 11, keyslog.count, 0 )) as November,
      sum(if( MONTH(keyslog.timestamp) = 12, keyslog.count, 0 )) as December,
      sum(keyslog.count) as Total
    from  customers, keyslog, users_customer
    where  (YEAR(timestamp) = '$year')
      AND  customers.id = keyslog.customers
      AND  users_customer.custo_id = keyslog.customers
      AND  keyslog.active = '1'
      AND  users_customer.userID = '$currentuserid'
      AND  users_customer.checked='1'
    group by  customers.id,customers.customername

As advised I tried to run explain and here is the result however I cant interpret it

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  keyslog     ALL     NULL    NULL    NULL    NULL    1437493     Using where; Using temporary; Using filesort
1   SIMPLE  customers   eq_ref  PRIMARY,id  PRIMARY     4   iccid.keyslog.customers     1   
1   SIMPLE  users_customer  ref     userID,checked,userID_2     userID_2    12  const,iccid.customers.id,const  1   Using where; Using index

Any suggestion on how to add any indexes on which tables or how to change the query to reach the best optimal retrieval would be much appreciated

Should I also specifically target the indexes that i add in the query for example

from customers,keyslog,users_customer USE INDEX (PRIMARY) ?

Upvotes: 2

Views: 60

Answers (1)

Rick James
Rick James

Reputation: 142208

users_customer: INDEX(userID, checked) -- unless userID is the PRIMARY KEY
keyslog: INDEX(customers)
customers: INDEX(id) -- unless it is already PRIMARY KEY.

Instead of (YEAR(timestamp) = '$year'), use

timestamp >= '$year-01-01'  AND
timestamp  < '$year-01-01' + INTERVAL 1 YEAR

(The optimizer is not smart enough to do the obvious thing with YEAR().)

For clarity, switch to the JOIN...ON syntax.

Learn about summary tables: http://mysql.rjweb.org/doc.php/summarytables

Upvotes: 1

Related Questions