RE: SQL indexing
The answer is a definite maybe. I'm Oracle familiar, not SQL Server, but if the data is skewed in any of those columns(90%=0 10%=1) then you may get some benefit when selecting the smaller value. Also, Oracle has special indexes for columns with low cardinality called bitmap indexes. These are amazing for static data, but can really slow down inserts and updates on these tables. You might want to see if SQL Server has something similar. HTH, Steve - Steven Monaghan Oracle DBA / Cold Fusion Developer MSC Industrial Direct Co., Inc. http://www.mscdirect.com - -Original Message- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 17, 2002 2:26 PM 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
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 __ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL indexing
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
