Re: manage large database
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.
Re: manage large database
can you please give me an example of such a query with example table with index? a very simple example please
Re: manage large database
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).
Re: manage large database
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.
Re: manage large database
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