Performance Codeigniter, + 2 million records.

Good morning people!

I have a problem with Code requests, when it has less than 1 million records, it loads considerably fast, starting from that mark it takes a long time to request.

A very important detail is that, even with this large number of records in the database table, it does not consume more than 1% of CPU and memory processing.

There is no limitation by the server, we test a benchmark and it consumes server resources normally.

Have you ever gone through this type of problem?

@[email protected] ,

My question to you would be have you done a database query analysis to make sure that your query/queries are efficient?

Codeigniter itself won't be the cause for slowness. I'd look at the following:

- Inefficient SQL queries
- Missing/Wrong SQL indexes
- Inefficient PHP code (n+1)
- Server CPU / Memory

Codeigniter is simply one of the tools you need to learn to be a successful developer. Always add more tools to your coding arsenal!

I put some columns as indexes inside MySQL to see if it improves, when I test without calculations or groupings inside the query it ends up being faster, today a table has more than 19 million records and ends up taking an average of 1m each query, today it has around 5/6 graphics to load.

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)

Thank you very much for the feedback.

@[email protected] ,

My question to you would be have you done a database query analysis to make sure that your query/queries are efficient?


- Good night, I tried to optimize the query issue, but even so it doesn't consume any 1% of processing, we also tested it in Cloud and it didn't work.

Whatever performance issues you are experiencing have nothing to do with CI4 or PHP.

Simpler is always better

Run your query in phpMyAdmin and use the explain command to see if it will speed it up.

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

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Run your query in phpMyAdmin and use the explain command to see if it will speed it up.



Good morning, I put this query in phpMyAdmin: EXPLAIN SELECT * FROM listeners WHERE updatedAt> = '2021-08-01 00:00:00' AND updatedAt <= '2021-08-12 23:59:59' AND connecttime> = 10

Returned the attached print, the return was instantaneous.

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

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

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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