The query looks custom built to take the longest possible time to execute
like steve said, use of the word "OR" is best kept to a minimum. If you think about it, it makes the questions rather non-specific, and increases the amount of searching (work) the db has to do. "LIKE" is the same. Indexes can't be used if you specify "like".
The select statement is also non-specific (select *).
If you want it to run well, change the question to something like 'select BILL_LNAME, BILL_FNAME from members where userid = 5'
That should run as quick as a flash if you've got an index on the userid column.
---
- The querry on the page looks like this >
- <cfparam name="LOOKUP" default="Y">
- <cfparam name="SearchTerms" default="">
- <cfparam name="TYPE" default="">
- <cfquery name="Search" datasource="toolshed" username="" password="">
- SELECT * FROM MEMBERS
- <cfif Lookup is "Y">
- WHERE USERID LIKE '%#searchterms#%' or
- BILL_FNAME LIKE '%#searchterms#%' or
- BILL_LNAME LIKE '%#searchterms#%' or
- BILL_COMPANY LIKE '%#searchterms#%' or
- BILL_ADDRESS LIKE '%#searchterms#%' or
- BILL_CITY LIKE '%#searchterms#%' or
- BILL_STATE LIKE '%#searchterms#%' or
- BILL_ZIP LIKE '%#searchterms#%' or
- BILL_PHONE LIKE '%#searchterms#%' or
- BILL_FAX LIKE '%#searchterms#%' or
- BILL_COUNTRY LIKE '%#searchterms#%' or
- BILL_EMAIL LIKE '%#searchterms#%'
- </cfif>
- ORDER BY BILL_LNAME, BILL_FNAME
- </cfquery>
- any pointers
- Thanks in advance
- regards greg stone
- ---
- You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
- To unsubscribe send a blank email to [EMAIL PROTECTED]
- MX Downunder AsiaPac DevCon - http://mxdu.com/
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
Web Developer, DBA
www.daemon.com.au ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
