Effect of index column on Mysql performance
TIPS: I changing the title of an article from “PHP-fpm ! I hate you” to “Effect of index column on Mysql performance” but some guys still watching previous title
I have been a PHP developer for many years. Employers want to build every platform with PHP!
Dear Employer, please stop build everything with PHP + Mysql.
PHP has a huge community around the world and many developers build their websites with PHP. some framework such as Laravel very nice and big and working with these frameworks is very comfortable.
WordPress is one of the big CMS of PHP, many developers and Employers love these CMS because they can build their website only in one day!
As example Ferraris vs. Jeeps both types are good and useful, in different situations. not using Ferraris for off-road usage.
This project different from the normal website
in this project, we have a pipeline for the AI process. I’m huge recommend using some technology such as Airflow to don’t face MySQL problems.
why index column on Mysql is important?
how many customers our clients will visit your website? this is the big question.
for example, we want to build a physical store in the city. many stores are tiny store upside of home and some store is Mega Market and they are huge.
WordPress is a common store and it looks like a small Store. when a website visits only 100 users in a day, WordPress is best and cheapest solution.
On other hand, Laravel is working with 100 users per second very well. but suddenly increase the number of clients we forced with 504 gateway error our website working very slow because of PHP-fpm Architecture.
Spending a Long Time to Handle 504 Gateway Error
if the business decided to develop a web service with PHP technology and meet a long number of user support and maintenance the PHP web service is very painful.
in this problem Mysql can’t response fast to queries
when I write simple query such as
SELECT *
FROM `tables`
WHERE `status_id` = 6mysql response
Caching
Actually, in this problem, we have to deal with lots of computational tasks and it is not easy to use caching for machine learning. In another problem, I use caching for Machine learning states with Redis but in this case, using caching for machine learning is a very difficult thing.
How we solve the problem
At first, we increase the number of CPUs and the size of the ram of the server. Secondly optimization query and add an index The most used column.
SELECT *
FROM `tables`
WHERE `status_id` = 6