Performance Codeigniter, + 2 million records.

On you from, the * is very slow in queries. Try using a field name to search on.

Good evening, the query with ( * ) I did only with EXPLAIN, when I do the query to return the information within the system, I just put the information I need. Also, what do you think it might be?

@[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)

@[email protected] ,

Wow!! you only have one index on this table and the field that is indexed isn't even used in your query. Here is a good read on indexes in MySQL (https://dev.mysql.com/doc/refman/8.0/en/...dexes.html). Based on how you will be searching this table (now and into the future) I would definitely add indexes to this table. One warning...every field in a table doesn't require an index this is why it is necessary to determine which fields should have an index. Indexes can slow your queries down. Based on the information that you have given us in this thread these are the fields that I would index: ID, CREATEDAT, UPDATEDAT, CONNECTTIME and UID.

Also, what is the difference of time between the two queries (before and after adding the indexes)? Also after you add the indexes run the EXPLAIN on your queries again and see the difference.

@[email protected] ,

Wow!! you only have one index on this table and the field that is indexed isn't even used in your query.  Here is a good read on indexes in MySQL (https://dev.mysql.com/doc/refman/8.0/en/...dexes.html). Based on how you will be searching this table (now and into the future) I would definitely add indexes to this table.  One warning...every field in a table doesn't require an index this is why it is necessary to determine which fields should have an index. Indexes can slow your queries down.  Based on the information that you have given us in this thread these are the fields that I would index: ID, CREATEDAT, UPDATEDAT, CONNECTTIME and UID.

Also, what is the difference of time between the two queries (before and after adding the indexes)? Also after you add the indexes run the EXPLAIN on your queries again and see the difference.



- Good afternoon, I've put in some indexes, but the 19 million records still appear in EXAPLAIN. Below is the print of the indexes.

Also after what @php_rocs has stated, I really do not think that you need those 2 bigint fields integer should be fine for them.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )