CODEWITHSUNDEEP
phpmysqlperformancelaravelsearch
Mustafa Ehsan Alokozay
Mustafa Ehsan Alokozay

Reputation: 5823

Dealing with millions of data records in MySQL and PHP/Laravel

I have an activity records table named revisions (showed in following image) built for a big learning management system, which mainly keeps record of CRUD operations on tables (e.g. who has done what on which object in what time).

revisions table

This table may contain up to 3M records of data. I want to build a search functionality for this on the front-end with PHP/Laravel.

Now my question is that what things should I consider for building search functionalities with high performance for tables with millions of records of data, what are the things on code level, database level, or are there 3rd party stuff to support these kind of issues?

I am experienced with building systems with PHP/Laravel, Python/Django, Ruby, etc. But I have never encountered with a case like this, dealing with millions records of data. So please keep in mind my knowledge/experience level. I have NO experience on this level.

Note: Search will be an advance search, making users able to search with different criteria and parameters, the object which is changed, who has changed it, when it's changed, etc.

Let me know if my question still isn't clear.

Upvotes: 3

Views: 2187

Answers (2)

Rick James
Rick James

Reputation: 142238

There is no problem inserting a zillion rows into a table. Performance problems come when you try to do non-trivial SELECTs on the table. You mentioned "search"; you will have to limit what the 'users' can search for. But at least make a stab at what they might want to search for.

You mentioned "searching for an object", but I don't see a column called object. How many rows might there be for a given object? Do you need all the rows? Or selected ones? (An INDEX on object is likely to make the query efficient, regardless of table size.)

Third-party software sometimes gets in the way of dealing with really large tables. Beware.

Upvotes: 4

lifekent
lifekent

Reputation: 300

I would recommend to take a look at the https://www.elastic.co/products/elasticsearch and save your activity records to its storage when you do save to the main database. Then you can easily search any field. Elasticsearch can store a schema free JSON documents, if you prefer more SQL way, there is another search engine - http://sphinxsearch.com/.

Upvotes: 4

Related Questions