Image trying to lookup a phone number in the white pages if the entries were in the order they were entered in to the database (ie...not in alphabetical order). You would have to look through the entire phone book to find any number. That's what your database has to do if you don't use indexed fields. For small tables, that's not a problem. With 27000 or 2.7 million records, it's a HUGE problem. I had a user in my users group once with a 150 MB Access database from a military supply mainframe. He imported the data everyday and came to the group complaining that he queries were taking 5-15 MINUTES to run. We looked at his database and discovered he had only text fields with no indexes. After we explained indexes to him his query times dropped dramatically.
Cheers Tom Nunamaker -----Original Message----- From: Darryl Lyons [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 17, 2002 3:04 PM To: CF-Talk Subject: RE: SQL indexing Indexing any field that is used to search with on a regular basis is always a good idea. For instance, I had a database full of users (about 27,000 of them) and the login process would take forever. Placing an index on the username and password fields increases the query execution speed by over 1000% (used to take over a minute, now around 1 second). The same principle would apply to any field where the server needs to lookup the table and return records based on the field in the where clause. If it has a pre-built index of rows where these values are, your query will run faster. Inserts of course will be a little slower as the index has to be rebuilt each time a new row is inserted. Regards Darryl -----Original Message----- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Monday, 18 February 2002 5:26 AM To: CF-Talk Subject: OT: SQL indexing Is there any benefit to indexing a "tinyint" field that is used as a boolean (either 1 or 0)? I have four fields that work this way and I use them heavily in queries for reporting. They are used in aggregate functions (count and sum) as well as in the where clauses for various queries. I know I could play with the indexes and test the timing, but I only have a live database to work with and don't want to disrupt anything until I feel there will be some significant benefit. Also, along the same lines, I report by grouping output (in SQL - not CF's group=) using the DAY, HOUR and MONTH parts of a datetime stamp. Will indexing help when using portions of a datetime field in this way, as opposed to considering the entire field? BTW - this is using SQL Server 2000 (and I guess the same theory would apply to SQL 7). Thanks! Jeff ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
