Welcome Guest, Not a member yet? Register   Sign In
CI for MySQL PHP migration project?
#6

(This post was last modified: 01-17-2020, 12:30 PM by jreklund.)

Hello, I have a migration project coming up and want to ask for feedback for my concept and also would like to know if CodeIgniter is the right choice.
- CodeIgniter are just like any other PHP Frameworks really. I think it's imported to look at the docs (CodeIgniter 4) and familiar yourself before actually choosing.

The client has a larger database (about 350 tables, some with around 100k datasets) in PostgreSQL 8 which is used by a stand alone application written in Pascal. This application is rather simple, it is just a frontend to the database with which you can create, modify, delete contents and print some info. The reason for migration is mainly that the application runs awefully slow.
- Are all tables accessed at all time? (do they use all 350 tables in all tasks)
- Do you make multiple joins?
- How many queries are you making on page load?
- Are they fetching the same data multiple times? Are some things static?

So my concept is using PHP 7.3 (thread safe), MySQL 8.0 (using PDO, mysqlnd) and CodeIgniter, mainly because of the speed aspect. I would start by redesigning the DB and GUI in communication with the client, which I would like to use MySQL Workbench for (any better alternatives here?). Once the DB is finalized, I imagine to have a certain function / method in CodeIgniter to generate PHP code from the DB-Schema -- is this possible (any sources for that)? The next step would be to customize and modify the automatically generated code and optimize it in terms of speed (any CI specific sources available for optimization, is this even necessary here?).
- PHP 7.4 have just come out (just so you know), there are for me only one feature that's interesting. That you can declare types for variables in classes.
- Personally I have installed MariaDB in all my servers.
- Are you going to display a DB flowchart/diagram to the client? Do they have programming/DB knowledge? If not I don't really see the need to involve them in that process.
- There are no function to generate models. You will need to write your own script after creating a base model and base everything on that.
https://codeigniter4.github.io/userguide...-databases
- If you want pure optimization you will need to write your own SQL queries (just be warned that you can't use the Entity classes etc if you do) and not depending on the Query Builder. But that goes for any framework. You should utilize are the Cache driver and cache the data and flush in on update/write (here is where you are getting the speed).
https://codeigniter4.github.io/userguide...ching.html

My personal goal is to have a quick and stable migration and make the DB fast accessible to the client. There is no need to optimize for some milliseconds and the users will not noteworthy increase in future. Do I even need to look for a fast framework like CI or could I use any other, because the main speedup will come from PHP 7.3 and MySQL 8? It's hard for me to estimate. I also thought of Laravel and Phalcon.
- CodeIgniter won't be faster or slower than any other framework out there. Those are just micro optimization. What really maters are your DB-server. You need to store pretty much everything in memory and utilize a good cache engine* for information that have been read from the DB.

Has someone experiences with Grocery CRUD (Enteprise)? Can someone recommend other systems like Grocery CRUD or are we better off not using such tools? Thanks.
- Only the open source. And that one are based on CodeIgniter 3. I would not recommend it for your project.

Make sure to have good database design with primary keys on things you are joining (and/or looking for). And don't join on anything else than integers (or binary). Also you NEED and I can't stress how much but you REALLY NEED to create your database with InnoDB no matter whatever you read on the internet are saying about MyISAM are faster for reading. With a good configuration on your DB server all your InnoDB content will be stored in memory and it will be blasting fast anyway.

The reason for this recommendation are that you can't make real time backup of MyISAM. If you are going to backup those type of engines you need to kill your application as nothing can access your database when preforming a dump.

I was hoping for a little more feedback / advice here, especially for the part where the database is supposed to be integrated in CodeIgniter.
- This is a Community forum (and rather small, but with some really dedicated people) and as this is a rather complicated matter I couldn't give you an answer during the week. Can't work with this full time, I really need to afford some kebab for dinner.

* I recommend Redis.

TLDR; Perfectly tuned MySQL/MariaDB server with InnoDB and utilizing a caching server e.g. Redis.
Reply


Messages In This Thread
CI for MySQL PHP migration project? - by Loomes - 01-14-2020, 12:21 PM
RE: CI for MySQL PHP migration project? - by jreklund - 01-17-2020, 12:27 PM



Theme © iAndrew 2016 - Forum software by © MyBB