manage large database

[chunk]

i have never come across the need of managing very large database.

but suddenly i got a question,

if i have a database with users table of a few millions users. so the searching will still go like this?

select * from users wherd id =1

or there are some special things to keep in mind while working on such database?

i have seen so many websites that go down so often when their database become so huge.

[jmcilhinney]

When your database is large, it becomes more important to ensure that your queries are efficient. Such a simple query as yours will be fine but more complex queries require thought and testing to make sure that they work as well as possible. It also becomes more important to place indexes on columns that will be used for searching purposes and to make sure that your queries make use of those indexes.

[chunk]

can you please give me an example of such a query with example table with index? a very simple example please

[si_the_geek]

The query will be exactly the same.

The index is something that you set up on the table itself (exactly how depends on the database system, see the table designer help for details). Once it is set up the database will automatically maintain it, and use it whenever appropriate.

Something to watch out for is that indexes aren't completely good. While they speed up queries that use the specific field(s) involved, they don't help with other fields, and more importantly indexes slow down inserts/updates/deletes of data (but if you run queries more often, the chances are that using indexes is better).

[Serge]

If you are searching for partial names for example (or any other text-like keywords), this query:
select * from mytable where lastname like 'Abc%' will not use an index and the query on a couple of million records will be extremely slow, use Full-Text index instead.

[chunk]

thanks you very much for the guide lines for indexes and LIKE(%)

please give me more tips, if you think there is something more for me to learn