Large table pagination?

[Alt_F4]

Hi all,

I need to retrieve records from a large table - we are talking 1 - 2 million rows and growing - i dont need all of the records just a the ones pertaining to the search criteria. The problem i have is that it takes ages to retrieve the data that the user needs (usually timeout occurs)

What is the best way to do this? I have had a look at pagination, which seems to probably be the best solution, but thought that I'd see what other options (if any were applicable)

Thanks

[LifeinAsia]

Properly index your table!

We have several tables with over 20 million records- no worries about timeouts.

Oh, and make sure your DB server has sufficient memory, processing power, etc.

[edited by: LifeinAsia at 11:20 pm (utc) on Oct. 16, 2008]

[tumr]

If you happen to be running MySQL 5.1, you could consider Partitioning, as well. But yes, like LifeinAsia said, this is probably an index problem. Try using EXPLAIN to see which indexes you're using, if any.

[Alt_F4]

Thanks for the replies!

How do I go about indexing my tables properly?

By the way I should mention that I am using MSSQL

[edited by: Alt_F4 at 11:13 pm (utc) on Oct. 21, 2008]

[rocknbil]

This may be a dumb question, but to eliminate the obvious . . .

I don't know if MSSQL has the same functionality (if it doesn't it should.) Are you using limit clauses?

select * from table limit 1000, 25;

will index right to record 1000 and pull only the next 25 records.

[LifeinAsia]

I don't know if MSSQL has the same functionality (if it doesn't it should.)

Not a dumb question- it doesn't. And you are correct, it most definitely SHOULD. I have several applications where I would love to use it.

How do I go about indexing my tables properly?

Indexing complex tables can be a science unto itself. Basically, you want to have indexes on the fields that you most often use for searching.

For example, if you have a table that you mostly search by a date or date (field name: date_added), make sure there is an index on the date_added field. If you have a table of address data that you often search by ZIPCode, make sure there is an index on the ZIPCode field.

The easiest way to add an index to a table in MSSQL is to use Enterprise Manager.

Note- you should definitely NOT be getting timeout errors on MSSQL on a table with only a few million records. Again, that's assuming you have sufficient memory and CPU capacity for what you're doing. If you have thousands of users hitting the DB every minute, you need a more powerful box than you would need with just a dozen users. And if you have thousands of simultaneous users, you may want to look into replication to spread the load between multiple servers.

[edited by: LifeinAsia at 3:58 pm (utc) on Oct. 22, 2008]