The applying must display records that are retrieved from an alphabetically purchased list, with a couple criteria distributed by user. Their email list is selected from several tables. The consumer may request next or previous page, or perhaps a particular page giving its number. Just one web site could have as much as 20 records. See my demo at http://18.104.22.168:1200
Classic internet tools (for instance, php and mysql) : bottleneck
If user requests page 5000, database server must choose the very first 100000 records. The clause LIMIT stop the search process after reaching this limit, and php script will get just the last 20 records. As we request for last pages of list, search process might take several minutes. How you can accelerate the search process to retrieve 20 records from middle / finish from the list ?
My solution uses C language
My webserver read at launch time the whole list in primary memory, then its prepared to accept connections. With the ability to start the search process from the given position, then locating the appropriate records. The webserver is running on Celeron 768 Mhz, 256 Megabytes memory, Linux Fedora operating-system.
I've browse the opinion of numerous web developers about using C language for web applications
Initially I implemented another search qualifying criterion (title starts with ...) and also the search process was extremely fast - about .015 seconds. The search process may also be slow should you match the area title consists of ... because when exhibiting page one, the webserver inspections the whole list.
The search problem was easily solved using C - a minimum of from my perspective. Now I must find a stylish means to fix solve the update problem - that's, simple to write and acceptable run time.
Does anybody know some solutions according to classic internet tools to resolve this issue in a way the response time be appropriate for any query ?
Through the sounds from it you're missing something fundamental inside your database access query, this should be trivial. If you wish to to gain access to page 5000 of the records you should not need to choose 100000 records. I am speculating that you're locating the records after which processing all of them with php to search for matches. Instead of do this, why don't you get mysql to complete the heavy-lifting with something similar to:
Choose * FROM communities WHERE society_title LIKE '%search_title%'
communities together with your database title,
society_title for that area that contains the society title and
search_title for that search phrase joined within the search box.
You might like to investigate a few of the free search engines like google. For example Lucene/Solr. I am unsure of if they'd match your problem, though.
You need to try to keep your index up-to-date, however the performance is great and you will find many integration options. You will also obtain access to a lot more search criteria, which happens to be an additional benefit.
This seems like something a good db would do in the sleep, you may can configure mysql in a different way or consider using a different db.