Page 1 of 1

Searching and Sorting: Need Advice on the Design.

Posted: Sat Jun 10, 2006 8:20 am
by ngungo
I have a wiki-kike project. It stores tens of thousands Vietnamese articles on a modified phpBB platform. For each article (equivalent to a post) there is a quadruple: post #, title, author, genre. I would like to be able to list them out in alphabetical order of titles or authors. How do I approach to it? Between flat file, database which way is more efficient?

I don't know what question to ask, but if you ask me question I will answer as best I know. I am a .NET programmer converting to php.

Posted: Sat Jun 10, 2006 9:42 am
by alex.barylski
Flat file over a database...

Depends...on what you mean by efficiency...

Programmer efficiency or code execution efficiency...

Obviously there is an overhead when using a database...compared to say writing a custom sort/search function...

However it is important to consider the following:
1) Is your code going to be executed in a distributed environment or will it be used by multiple users atthe same time.
2) Is programmer efficiency or project longevity more important than efficiency

If your writing an application in PHP using a SQL RDBMS like MySQL or SQLite is the obvious choice...why?

1) Your applications are 95% chance of being used concurrently
2) PHP, by it's very nature executes slowly...so using a RDBMS which is likely writen in C will carry out searching and sorting tasks much quicker than native PHP code

Unless you really know what you are doing, writing custom search/sort functionality, especially reading/writing is tricky...

For instance, you have to concern yourself with file locking, security, etc...

RDBMS like mySQL are designed to run in distributed environments...they can take advantage of RAID or whatever the latest rage is...they will maximize the use of hardware, which would be difficult to do on your own...

SQL code is likely very reliable and error free and SQL statements make more sense than custom filtering code depending on the language you write it in.

SQL is portable, whereas, your custom filtering code is not...if you write it in PHP and later decide to port to C...it's more difficult to port PHP to C code than it is to just copy the SQL statements to your C application.

SQL offers scalability...you can easily switch RDBMS from a single file, single user, super fast and efficient SQL engine like SQLite to a multiple user, security centric full blown RDBMS like MySQL or MSSQL in the blink of an eye...

Anyways...the answer should be obvious...use SQL don't bother writing your own custom search/sort/filter type functions...unless you absolutely have to...but ifyour using PHP as a language you don't need too :)

HTH

Cheers :)

Posted: Sat Jun 10, 2006 9:55 am
by MrPotatoes
ugh. file locking...

Posted: Sat Jun 10, 2006 9:57 am
by ngungo
Hi Hockey,

I appreciate and thank for the quick response. I need time to digest your response.

Posted: Sat Jun 10, 2006 12:05 pm
by alex.barylski
ngungo wrote:Hi Hockey,

I appreciate and thank for the quick response. I need time to digest your response.
Take some of that pink stuff to prevent heart burn while digesting ;)

Posted: Sun Jun 11, 2006 12:39 am
by alvinphp
The database (using a stored procedure) will be much more efficient because you can index the table you plan to search and the search is done by the database versus your PHP code.

Posted: Sat Jul 29, 2006 10:33 pm
by ngungo
Thanks folks,

I have implemented by flat file method. It works beautifully but I am thinking to revamp it to database for scalability since I kind of understand SQL better.

Thanks again!

Posted: Sun Jul 30, 2006 5:27 am
by timvw
Since you want maximum performance i advise that you serve static html to the visitors... And everytime someone submits a change update the static pages that have changed...

Posted: Sun Jul 30, 2006 9:08 am
by ngungo
Yeah! Yeah! Genius idea!

Posted: Sun Jul 30, 2006 9:52 am
by Ambush Commander
It's called... caching!