CodeIgniter Forums
Performance Codeigniter, + 2 million records. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Performance Codeigniter, + 2 million records. (/showthread.php?tid=79832)

Pages: 1 2

Performance Codeigniter, + 2 million records. - [email protected] - 08-02-2021

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?

RE: Performance Codeigniter, + 2 million records. - php_rocs - 08-02-2021

@[email protected] ,

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

RE: Performance Codeigniter, + 2 million records. - albertleao - 08-02-2021

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

RE: Performance Codeigniter, + 2 million records. - [email protected] - 08-10-2021

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.

RE: Performance Codeigniter, + 2 million records. - [email protected] - 08-11-2021

(08-02-2021, 03:46 PM)php_rocs Wrote: @[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.


RE: Performance Codeigniter, + 2 million records. - donpwinston - 08-12-2021

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

RE: Performance Codeigniter, + 2 million records. - InsiteFX - 08-12-2021

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

RE: Performance Codeigniter, + 2 million records. - [email protected] - 08-12-2021

(08-12-2021, 03:08 AM)InsiteFX Wrote: 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.

[Image: explain.png]


RE: Performance Codeigniter, + 2 million records. - InsiteFX - 08-13-2021

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

RE: Performance Codeigniter, + 2 million records. - php_rocs - 08-13-2021

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