How to effectively search 1.6 million rows?

[ajs83]

With a database containing 1.6 million records, what's the most effective way to search it without the page taking forever to run?

[R e b r a n d t]

It is a very abstract question... so the answer would be: write effective queries.

For a more detailed answers readers would need to know:
a) what kind of 'search' is intended to be done: text search, full text search, index/key search, combined/any kind of search?
b) what kind of data structure you have
c) what kind of DBMS you have
d) what is your definition of the word 'effectively'? Is 1 second for the results enuff or not?

p.s.: should not this go to Database forum?

[ajs83]

It is a very abstract question... so the answer would be: write effective queries.
For a more detailed answers readers would need to know:
a) what kind of 'search' is intended to be done: text search, full text search, index/key search, combined/any kind of search?
b) what kind of data structure you have
c) what kind of DBMS you have
d) what is your definition of the word 'effectively'? Is 1 second for the results enuff or not?

A) A title text seach
B) It's a simple title - description - status structure
C) PhpMyadmin
D) Something that is not going to take a lot of time where the end user gets frustrated by the waiting.

p.s.: should not this go to Database forum?

I'm using php code to design the page so I figured it would be better suited here.

[grandpa]

What kind of recordset are your returning? Single row, multiple rows? If your Title field is indexed the search should be pretty quick, especially for a single row of data.

If you are returning multiple rows then use GROUP BY on the Title to select only those rows. Title should still be indexed.

I run a query over 3/4 million rows, searching for a text pattern within a given field - not the most efficient query in the world. The result is returned very quickly, displaying those results on the page takes about 3 seconds.

[StupidScript]

What brand of database software are you using? MS-SQL? MySQL? PostGre? Other? Each has different optimization quirks. Indexing the primary field is a great idea. 1.6M records sounds like a lot ... and it is ... but it's not humongous. Most database apps can handle that many records (depending on the complexity of the record ... how many fields, etc.) without breaking a sweat. (Not MS).

How fast is it, now?

And Rebrandt is right ... this might be more of a database question than a PHP question. The PHP front-end will pull the data and the query structure does make a difference, but your main bottleneck(s) will be in the database, not on the web page.