0

The application must display records which are retrieved from an alphabetically ordered list, using some criteria given by user. The list is selected from two or more tables. The user may ask next or previous page, or a particular page giving its number. A single web page may contain up to 20 records. See my demo at http://193.226.19.29:1200

Classic web tools (for example, php and mysql) : bottleneck

If user asks for page 5000, database server must select the first 100000 records. The clause LIMIT will stop the search process after reaching this limit, and php script gets only the last 20 records. If we ask for last pages of list, search process may take several minutes. How to speed up the search process to retrieve 20 records from middle / end of the list ?

My solution uses C language

My webserver read at launch time the entire list in main memory, then it is ready to accept connections. It is able to start the search process from a given position, then retrieving the needed records. The webserver is running on Celeron 768 Mhz, 256 Mb memory, Linux Fedora operating system.

I have read the opinion of many web programmers about using C language for web apps

Why do you not use C for your web apps?

How popular is C++ for making websites/web applications?

Initially I implemented another search criterion (name begins with ...) and the search process was very fast - about 0.015 seconds. Now the search process is sometimes slow if you fulfill the field name contains ... because when displaying the first page, the webserver checks the entire list.

The search problem was easily solved using C - at least from my point of view. Now I have to find an elegant solution to solve the update problem - that is, easy to write and acceptable run time.

Does anybody know some solutions based on classic web tools to solve this problem in such a way that the response time be acceptable for any query ?

Community
  • 1
  • 1
Nelu Cozac
  • 1

3 Answers3

4

By the sounds of it you are missing something fundamental in your database access query, this really should be trivial. If you want to to access page 5000 of your records you shouldn't have to select 100000 records. I'm guessing that you are retrieving the records and then processing them with php to look for matches. Rather than do that, why not get mysql to do the heavy lifting with something like:

SELECT * FROM societies WHERE society_name LIKE '%search_name%'

Just replace societies with your database name, society_name for the field containing the society name and search_name for the search term entered in the search box.

Lawrence Woodman
  • 1,402
  • 9
  • 13
0

You might want to investigate some of the open source search engines. For instance Lucene/Solr. I'm not entirely sure if they'd fit your problem, though.

You do need to find a way to keep the index up-to-date, but the performance is excellent and there are many integration possibilities. You'll also get access to many more search criteria, which could be an additional benefit.

Peter
  • 740
  • 4
  • 18
0

This sounds like something a decent db would do in its sleep, maybe you can configure mysql differently or try a different db.

Tom
  • 39,281
  • 4
  • 35
  • 60