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 ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
