Optimize to the maximum mysql

1. Use latest MariaDB
2. In phalcon use joins etc when possible(don't relate on relations)
3. Select data only you need(columns)

About how to configure mysql/mariadb for performance etc you can just google it

I'd also add that pagination can have three sources: array, model and query builder. When paging a large number of results, you should ALWAYS use QueryBuilder!

https://docs.phalcon.io/en/latest/reference/pagination.html

In wamp I am using mysql 5.7.14 It's enough?

To start, if from phpmyadmin login to view the sales table with 5million registration, the first page shows it immediately. But if I go to the last page it takes:

Showing rows 4475300 - 4475300 (4475301 total, Query took 11.6134 seconds.)

Without doing any join or anything is already taking 11 seconds. The minimum structure of the table is: Table name: sales Primary key: NUM Other fields, a, b, c, etc.

The query I'm running is: SELECT NUM FROM SALES

But if I remove the revision of foreign keys takes between 1.6sec and 2.6sec.

SET FOREIGN_KEY_CHECKS = 0;

I definitely do not use CASCADE since it is a web system and has to go extremely fast.

Is it okay to disable the patch?

If I have to say that I use indexes: PRIMARY_KEY, INDEX and UNIQUE

You better use mariadb 10.1, also it will always take long to select last page in phpmyadmin

Also try to avoid OFFSET/TOP queries if it's possible, faster will be INDEX ON column + where + limit.

For example assume we have paging, instead of doing standard for 5th page:

SELECT columns OFFSET X LIMIT Y

You should try to make it if possible:

SELECT COLUMNS WHERE id > LAST ID OF 4th page LIMIT Y

This will be much faster, especially for latest pages, you could use it without any problem on some lazy loading stuff, it will be hared to use it for any page, not sure how exactly your tables looks like but if you can anyhow avoid TOP/OFFSET queries do it, and if you can't - try to change your database/application logic to make it stop using top/offset.

https://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html here you can read about configuration

https://mysql.rjweb.org/doc.php/pagination

This is great article about this problem

Actually query builder pagination in phalcon is not optimized for big tables, YOU SHOULD NEVER use offset/top for big tables for pagination.

I'd also add that pagination can have three sources: array, model and query builder. When paging a large number of results, you should ALWAYS use QueryBuilder!

https://docs.phalcon.io/en/latest/reference/pagination.html

OK thanks. I'm going to put it into practice to see if it works for me. What if I can say that disabling the FOREIGN_KEY_CHECKS speed difference is 10 to 1. But I do not know if it's right to work like this?

You better use mariadb 10.1, also it will always take long to select last page in phpmyadmin

Also try to avoid OFFSET/TOP queries if it's possible, faster will be INDEX ON column + where + limit.

For example assume we have paging, instead of doing standard for 5th page:

SELECT columns OFFSET X LIMIT Y

You should try to make it if possible:

SELECT COLUMNS WHERE id > LAST ID OF 4th page LIMIT Y

This will be much faster, especially for latest pages, you could use it without any problem on some lazy loading stuff, it will be hared to use it for any page, not sure how exactly your tables looks like but if you can anyhow avoid TOP/OFFSET queries do it, and if you can't - try to change your database/application logic to make it stop using top/offset.

https://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html here you can read about configuration

https://mysql.rjweb.org/doc.php/pagination

This is great article about this problem

I don't think you can disable them globally, doing this will only disable them for current session. And it's not good idea to disable them anyway. Just update your mysqldb 5.7 to mariadb 10.1 first.

Also remember to use mysql query cache, just don't disable it by mistake.

Ok let's try

Thank you