How to handle increasing data over time in MySQL and PHP

Question

I have a list of contents on my website and it is getting filled with records rapidly, for example after one month, there are 300K records in it. Because of this, the list page is responding slower and slower over time.

This page has this functionalities:

Searching
Paging

Also this table Left Joins other tables and it increases the run-time.

I run two queries every time this page is loaded, one for getting 10 limited records, and the other for getting number of all records.

How do I handle this amount of data with out jeopardizing user experience?

EDIT

Here is my Query:

SELECT *,
    `note`.`attached_file` AS `attached_file`,
    `note`.`description` AS `description`,
    `note`.`id` AS `id`,
    `note_type`.`title` AS `title`,
    `note_goal`.`title` AS `goal`
FROM `note`
    LEFT JOIN `contact` ON `note`.`id_contact`=`contact`.`id`
    LEFT JOIN `contact_activity_field` ON `contact_activity_field`.`id_contact`=`contact`.`id`
    LEFT JOIN `activity` ON `contact_activity_field`.`id_activity`=`activity`.`id`
    LEFT JOIN `note_type` ON `note`.`title`=`note_type`.`id`
    LEFT JOIN `note_goal` ON `note`.`goal`=`note_goal`.`id`
WHERE
    ( `note_type`.`title` LIKE '%$q%' OR
    `firstname_eng` LIKE '%$q%' OR
    `lastname_eng` LIKE '%$q%' OR
    `firstname_per` LIKE '%$q%' OR
    `lastname_per` LIKE '%$q%' OR
    `company_name` LIKE '%$q%' OR
    `company_name_per` LIKE '%$q%' OR
    `description` LIKE '%$q%' OR
    `note_goal`.`title` LIKE '%$q%' ) AND some other condition
    GROUP BY `note`.`id`
    ORDER BY `note`.`id` DESC
    LIMIT $start_from, 10

EDIT 2

note table

CREATE TABLE `note` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `id_user` int(10) NOT NULL,
 `id_contact` int(10) NOT NULL,
 `title` int(10) DEFAULT NULL,
 `goal` int(10) DEFAULT NULL,
 `register_date` date DEFAULT NULL,
 `description` text COLLATE utf8_unicode_ci,
 `attached_file` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `meeting_place` text COLLATE utf8_unicode_ci,
 `start_time` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `finish_time` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3297 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

contact table

CREATE TABLE `contact` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_user` int(10) NOT NULL DEFAULT '0',
 `id_user_registered` int(10) DEFAULT NULL,
 `code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `contact_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT 'legal',
 `firstname_eng` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lastname_eng` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `firstname_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lastname_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `gender` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `id_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `national_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `birth_date` date DEFAULT NULL,
 `company_name` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `company_name_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `recommender_eng` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `recommender_per` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `company_type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `registration_type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `registration_date` date DEFAULT '1900-01-01',
 `registration_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `national_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `economic_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `website` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `postal_code` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_eng` text COLLATE utf8_unicode_ci,
 `address_per` text COLLATE utf8_unicode_ci,
 `phone_number` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `email` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `fax` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `activity_comment` text COLLATE utf8_unicode_ci,
 `level` varchar(50) COLLATE utf8_unicode_ci DEFAULT 'Basic',
 `guild` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `verify_comment` text COLLATE utf8_unicode_ci,
 `status` int(1) NOT NULL DEFAULT '0',
 `comment` text COLLATE utf8_unicode_ci,
 `submitted` int(1) NOT NULL DEFAULT '0',
 `assign_date` datetime DEFAULT NULL,
 `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57357 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And other tables just contain a simple id and title records.

Here is the result of EXPLAIN query:

These results are on my local database.

normally good indexing related to the where and join condition solve the problem .. but your question i too broad .. try add the table schema and the query code.. and the execution plan too — ScaisEdge, Aug 10 '19 at 16:51
300K records is nothing for a modern RDMS, MySQL should be able to handle millions or billions or records just fine when indexed and MySQL uses it. — Raymond Nijland, Aug 10 '19 at 16:51
@RaymondNijland I'm just worried for my website in long term. — Alireza A2F, Aug 10 '19 at 16:52
also the "Big Data" term is a bit wrong here in mine opinion, i personally use this term to indicate data which is bigger then the servers RAM memory.. Meaning 5 Gb data on a 4 Gb machine can be considerd "Big data" but on a 8 Gb RAM machine it fits into the memory just fine when configured correctly. Also this question is unclear to be anwerd without table structures and the used queries.. — Raymond Nijland, Aug 10 '19 at 16:53
@RaymondNijland Thanks for your kind comment. I will edit my question. — Alireza A2F, Aug 10 '19 at 16:56
we need table structures -> `SHOW CREATE TABLE table` fo every table involved in the query/question and a `EXPLAIN query` output.. But i can tell you one thing already `LIKE '%..%'` pattern can never be optimized consider using FULL TEXT indexing and full text searching with `MATCH() AGAINST()`.. Also consider reading [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) as your current code looks to be prone to SQL injections? — Raymond Nijland, Aug 10 '19 at 16:57
Why do you run two queries? You could execute one query and filter out the first 10 posts instead. It would be a lot quicker. How exactly do you execute your SQL? — bestprogrammerintheworld, Aug 10 '19 at 16:59
@bestprogrammerintheworld I believe this would take much more RAM to store result of all the records. — Alireza A2F, Aug 10 '19 at 17:01
@AlirezaA2F - why? You still have to run two queries... that take more resources than running one query. Can you show us what code you use to execute your queries? It might be clearer what you mean. — bestprogrammerintheworld, Aug 10 '19 at 17:03
@bestprogrammerintheworld Can you explain your solution more? — Alireza A2F, Aug 10 '19 at 17:07
@AlirezaA2F - I can't help you if you don't show any code. Please show code so we can help. — bestprogrammerintheworld, Aug 10 '19 at 17:08
This is not how you should be using [GROUP BY in MySQL](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) .. Well unless the expection functional dependence can be used in MySQL 5.7.5+ then the SQL is valid..Also as most likely the GROUP BY is used invalid in your SQL, we also need example data and expected results.. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Aug 10 '19 at 17:16
@bestprogrammerintheworld I run the same query without `LIMIT` and get `num_rows()` using PHP. — Alireza A2F, Aug 10 '19 at 17:34
You don't even have indexes to support the joins. Start there. — Paul Spiegel, Aug 10 '19 at 17:49
A good tip is: NEVER use evil `SELECT *` And in the absence of any aggregating functions, don't use a GROUP BY clause. Use DISTINCT instead. — Strawberry, Aug 10 '19 at 18:07
i doubt that DISTINCT will help @Strawberry as `GROUP BY id` is a "unduplication" on the id column only.. I wished MySQL supports PostgreSQL's `DISTINCT ON(id), *` syntax native well atleast you can [simulate](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691) it with a SUBSTRING and GROUP_CONCAT/GROUP BY in MySQL/MariaDB. — Raymond Nijland, Aug 10 '19 at 18:10
@RaymondNijland It won't make the query faster, but it will make the result less indeterminate — Strawberry, Aug 10 '19 at 18:12

score 2 · Accepted Answer · answered Aug 10 '19 at 17:17

LIKE with a leading wild card is inefficient -- and will scan the entire table, hence the "slower as it gets bigger". Switch to FULLTEXT.
Pagination via OFFSET is inefficient because it must scan all the rows befoer the few desired. See http://mysql.rjweb.org/doc.php/pagination
Both of these have been discussed on stackoverflow repeatedly. Search for other discussions.

How to handle increasing data over time in MySQL and PHP

1 Answers1