Performance Codeigniter, + 2 million records.

@[email protected]
What version of CI are you using? What version of MySQL are you using?

Also, what fields are indexed in the listeners table?

The EXPLAIN is telling you that you are actually going through 14283406 rows of data to find your solution (That's really bad).  It is basically telling you that you need indexes.


Good night, below are the images of how the indexes are doing.





I tried to put ipclear which is the user's IP without . and - I usually group by dates and/or times.

Below is a query that takes a long time to respond, around 1 to 2 minutes.

Below is the query that goes faster:

$db2->select('SQL_NO_CACHE COUNT( uid ) as totalListeners, DATE_FORMAT(updatedAt, "%d/%m/%Y") as updatedAt');
$db2->where('updatedAt >', $post['date_start']);
$db2->where('updatedAt <', $post['date_end']);
$db2->where('connecttime <=', $post['timeMax']);
$db2->group_by('DATE_FORMAT(updatedAt, "%Y-%m-%d")');

And the query that takes a long time, depending on the number of records, takes more than 3 minutes:

SELECT SUM(connecttime) AS connecttime, DATE_FORMAT(updatedAt, '%H:00') as updatedAt FROM listeners WHERE updatedAt>='{$post['date_start']}' AND updatedAt<='{$post['date_end' ]}' AND connecttime >= '{$post['tempoMin']}' GROUP BY HOUR(updatedAt)