best fastest way for database record search

Hi Guys,

I will be working on a database in the hosted server with 50 millions records and 30 columns.

What do you recommend the best fastest way to retrieving records?

You would need to give us much more detail as your question is too generic.

SQL: what else.

But this is like asking if Xojo is fast or slow. You need an understanding how SQL works to ensure that your SQL is fast enough.

Make sure you index the correct colums.

Are you hoping for ‘search which will turn up a row where some value exists in any of the fields’?
Thats known as a full string search, and isn’t fast.
It can’t really be indexed either, since the thing you are searching for could be at any position in the string.

If you are looking for exact matches in some of the columns, index those columns.

If you are looking for partial matches, it always has to walk the whole table.

I’m guessing a 50 million row database is too large to hold in memory, so hope that the hosted server has a fast drive and a decent network connection.
If you send a SQL query to the host and only get back the results,
that is much faster than
opening the database as a recordset and walking through the rows in your code

(because that means all the data in the database has to be transferred to your machine during the search)

For a project I had a SQL Server 2005 with 80 million records and 30 record-updates / second in busy times. With a little help of a DBA, some stored-procedures and sharp indexes, we got a very good performance. I know of a quite similar project doing this with PostgreSQL.

If you’re not familiar with SQL and optimizing both your database design and your queries, hire someone to help you. Just a few hours of work from an expert may be enough to improve performance.

On the hardware level: SSDs. Databases love SSDs. Just have a solid backup or redundancy plan because when you lose a SSD, you really lose it. No heroic data recovery efforts like with HDDs.

Hi Guys,

Thanks for all your response.

Just to clarify my question, here is my scenario.

CLIENT REQUIREMENT: My Client requires me to create a Web app where he can search a record from partial string he typed on a textbox. Potential users of the system is not less than 30 persons.

CURRENT SCENARIO:

  1. I am planning to develop this requirement using XOJO web application.
  2. Af of now, I am using shared server with MYSQL on it.
  3. My Client told me that he will give csv file for all the records they have.
  4. I will definitly import it to my MYSQL databse.

That’s it!

I hope you guys give me some best plan and strategy for a seamless solution.

Something called middleware was discussed a couple of times here on the forum. There are also frameworks available and there is a Xojo webinar for this topic.

For the partial string searching you will need this here: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html .

And remember to do your due diligence in regards to the §$%& MySQL license.

yep better use postgresql for safe $$$ future.

depending on the search criteira, you could create a psuedo index by parseing all the main records into single words, and indexing that…

but barring that, a proper index scheme… and bear in mind, JUST BECAUSE you created an particular index, does NOT mean a given query will or will not use it… Become familiar with the EXPLAIN PLAN feature of what ever database you are using