How to use indexing?

I have an MS Access db with 100K records. If I query on 10 fields in this db that my performance is real slow. Can I use indexing on the fields to speed things up? Should I index all 10 fields that I query? Thanks.

Are the 100K records in one table or two or more related tables?

You need only index a field or fields that uniquely identify each record you are querying. Usually Access does this for you when you create keys.

Access with 100K records is automatically going to be a problem. It was never really intended for large databases.

Once you get to that number of records, it is better to go with a real client-server database system, such as SQL Server, or any number of SQL DBMS systems.

rycamor-

Is it more realistic to use MySQL? This db will continue to grow beyond 100K records.

It will be more realistic than Access anyway, see my signature for other opensource database options.
Regards

I think you are getting Primary key and an index mixed up. A primary key is just 1 type of index.

Indexes can also be used to speed up queries but it's the where clause that's important. You would want indexes on the columns or combination of columns that limits the selection.

I would also agree that access is not suited for this purpose, it is not a real database it's purpose is really limited to storing your grandma's recipe collection not 10000's of rows.

I don't have much use for MySQL, I would look at PostgreSQL if you want something free. If money is no object then Oracle is the clear choice.

It really depends on your needs. I should mention though that actually MySQL does not have all the capabilities of Access. (What!!! Outrage!!!) Yes, it's true. MySQL can definitely handle many more records than Access, but it doesn't have Views (they are called Queries, in Access). This means that you are always stuck dealing directly with the base tables in MySQL.

There are plenty more lackings in MySQL compared to the other DBMS systems in pabloj's signature. But, if your database is fairly simple, and you don't expect it to grow in complexity later, then MySQL might be the right choice for you.

I usually use MySQL w/ PHP but the db I was given was MS ACCESS. So should I index the columns that I will use in all of my where clauses? What happens if I use all of my field names in my where clause...will adding indexes to all fields slow down the query even more? Thanks.

This is hard to answer without knowing the stucture of your tables. Maybe if I can explain how indexes work you can decide for yourself. I apologize if this is old hat.

An index is just a subset of the table stored in a 'tree' structure which makes it very fast to navigate.

If the query processor can navigate this tree to determine if records should be included instead of scanning the entire table then it will improve select performance.

Indexes can be made of single columns or concatenated to include multiple columns. So if you are constantly querying a table based on the same combinations of columns then a concatenated index will help. <b>Note:</b> individual indexes on each of the seperate columns is not the same thing.

As in everything indexes have a trade off, the more indexes you have the worse the performance of inserts/update because the index has to be rebuilt.

Try to build your indexes based on quality not quantity.

If you have any other more specific questions I'll try to give you an explanation if I can.

Not sure if a multi-column index would work since I am using "REGEXP" and "=" throughout query. Here is my sql statement (very long):

Aha!

It is almost impossible for REGEX-dependent queries to use indexes anyway, so that would probably account for the slowness.

Even some of the more advanced DBMS systems are unable to use indexes to speed up the query. Think about it: with all the possible combinations, an index based on REGEX would have to be huge, potentially much bigger than the table itself.

As I stated before, I think you have hit the area of diminishing returns for an Access database. Even though the more powerful SQL systems can't use indexes for REGEX, they are still much, much faster than Access.

Also, although REGEX is a performance bottleneck, one of the things that the more powerful SQL systems can offer is full-text indexing, available for MySQL and PostgreSQL, as well as many commercial systems. Any database where it is important to search through large amounts of text will benefit from full-text indexing.

I came to realization on using MySQL. After moving the data over the performance really enhanced. I know of Full_text indexing. I will have to look into it. Thanks.