Hi there. I've been working with a table that has over 50 fields and over 300,000 rows which is much more than anything I've ever worked with in the past. My issue is with speed. I run the following query and it takes 5 or 10 seconds to return on my local machine. I've indexed each individual field I use in my query but haven't done any caching since there are so many different search combinations and it would be rare that a certain search happens often.
I'm hoping someone can make recommendations on how I can optimize this query or anything else I could do to increase performance.
FMCSA table is the table with over 300,000 records in it. The FMCSA_LICENSED_LINK table is a linking table that stores different "types" assigned to a record in the FMCSA table. A single record in the FMCSA table may have 10 or more records in the FMCSA_LICENSED_LINK table. I have indexed this table as well.
SELECT census_num, name, name_dba, phy_city, phy_st, emailaddress, tel_num, cell_num, tot_trucks, iccdocket1
FROM fmcsa LEFT JOIN fmcsa_licensed_link ON fmcsa.CENSUS_NUM = fmcsa_licensed_link.dot_id
WHERE PHY_NATN = '#country#'
<cfif #interstate_only# eq "Y">
AND ICCDOCKET1 = 'MC'
AND PHY_ST = '#state#'
AND census_num IN (SELECT DISTINCT(dot_id)as census_num
WHERE licensed_type_id IN(<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(permitted_to_haul)#" list="yes">))
<!--- Radius Search Calculation --->
AND 3963.191 * ACOS((SIN(PI() * #coordinates.latitude# / 180) * SIN(PI() * fmcsa.latitude / 180)) + (COS(PI() * #coordinates.latitude# /180) * cos(PI() * fmcsa.latitude / 180) * COS(PI() * fmcsa.longitude / 180 - PI() * #coordinates.longitude# / 180)) ) <= #distance#
GROUP BY CENSUS_NUM