#1
  1. Charge into the Knight
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    Orlando, FL
    Posts
    276
    Rep Power
    13

    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.
    Every man is a fool for at least five minutes every day; wisdom consists of not exceeding the limit.-- Elbert Hubbard
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Washington State, USA
    Posts
    2
    Rep Power
    0
    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.
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. Charge into the Knight
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    Orlando, FL
    Posts
    276
    Rep Power
    13
    rycamor-

    Is it more realistic to use MySQL? This db will continue to grow beyond 100K records.
    Every man is a fool for at least five minutes every day; wisdom consists of not exceeding the limit.-- Elbert Hubbard
  8. #5
  9. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    Originally posted by Brian1
    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.
    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.
    Last edited by hedge; January 10th, 2003 at 08:55 AM.
  12. #7
  13. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by bnow
    Is it more realistic to use MySQL? This db will continue to grow beyond 100K records.
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  14. #8
  15. Charge into the Knight
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    Orlando, FL
    Posts
    276
    Rep Power
    13
    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.
    Every man is a fool for at least five minutes every day; wisdom consists of not exceeding the limit.-- Elbert Hubbard
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    Originally posted by bnow
    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.
  18. #10
  19. Charge into the Knight
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    Orlando, FL
    Posts
    276
    Rep Power
    13
    Not sure if a multi-column index would work since I am using "REGEXP" and "=" throughout query. Here is my sql statement (very long):
    PHP Code:
    SELECT DISTINCT
    materials
    .main_entry_personal_name
    materials.title_statement
    materials.isbn
    materials.publication
    materials.date_of_pub,
    materials.local_call_number
    materials.mat_rec_id as theid FROM 
    (materials LEFT OUTER JOIN scndry_authors ON materials.mat_rec_id=scndry_authors.mat_rec_idLEFT OUTER 
    JOIN scndry_isbns ON materials
    .mat_rec_id=scndry_isbns.mat_rec_id WHERE 
    ((materials.title_statement REGEXP '[[:<:]]okeefe') OR 
    (
    materials.subtitle REGEXP '[[:<:]]okeefe') OR 
    (
    materials.alternate_title REGEXP '[[:<:]]okeefe') OR 
    (
    materials.main_entry_personal_name REGEXP '[[:<:]]okeefe'
    OR (
    materials.publication 'okeefe') OR
    (
    materials.date_of_pub 'okeefe') OR 
    (
    materials.add_publisher 'okeefe') OR 
    (
    materials.isbn 'okeefe') OR
    (
    materials.local_call_number 'okeefe') OR 
    (
    scndry_authors.personal_name_added_entry REGEXP '[[:<:]]okeefe') OR 
    (
    scndry_isbns.isbn_2 REGEXP '[[:<:]]
    okeefe'
    )) 
    ORDER BY materials.title_statement LIMIT 0,100 
    Every man is a fool for at least five minutes every day; wisdom consists of not exceeding the limit.-- Elbert Hubbard
  20. #11
  21. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  22. #12
  23. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  24. #13
  25. Charge into the Knight
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    Orlando, FL
    Posts
    276
    Rep Power
    13
    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.
    Every man is a fool for at least five minutes every day; wisdom consists of not exceeding the limit.-- Elbert Hubbard

IMN logo majestic logo threadwatch logo seochat tools logo