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.
(08-02-2021, 03:46 PM)php_rocs Wrote: @[email protected] ,
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 )
(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.
@[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. |
Welcome Guest, Not a member yet? Register Sign In |