What can I do to make this query run faster? MySQL 5, Coldfusion 9

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.
<cfquery name="fmcsa">
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'
</cfif>
AND PHY_ST = '#state#'
AND census_num IN (SELECT DISTINCT(dot_id)as census_num
				FROM fmcsa_licensed_link
				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
LIMIT 100
</cfquery>

Open in new window

MFredinAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
I may be wrong here. ...  (It's not clear which columns belong to which table).  but why do you need a subquery at all? You're already JOINing to fmcsa_licensed_link.  It seems like you could switch it to an INNER JOIN, then filter on
licensed_type_id.

(Not tested ...)

FROM     fmcsa INNER 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'
</cfif>
AND       PHY_ST = '#state#'
AND      licensed_type_id IN(<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(permitted_to_haul)#" list="yes">)

... rest of query ....
0
 
HyperBPPCommented:
Query looks pretty good.

You might checkout some MySQL best practices for performance.
http://forge.mysql.com/wiki/Top10SQLPerformanceTips
http://www.devshed.com/c/a/MySQL/Optimizing-for-Query-Speed/
0
 
KarlisBCommented:
i dont like this part:

AND census_num IN (SELECT DISTINCT(dot_id)as census_num
                                FROM fmcsa_licensed_link
                                WHERE licensed_type_id IN(<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(permitted_to_haul)#" list="yes">))
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
KarlisBCommented:
that part is really terrible if "in" statement has dozen of values like 20 or more to check.
try

why dont you use  "exists" instead of that first in statement, that should give great gain of speed.

in statement will return all values matching
exists instead will check the weather if there any value is found and by first match it stops searching  returns true.

since you need to check if there are any census_num value in fmcsa_licensed_link table,
in statement will return.

and there is something else bunging me ....
0
 
MFredinAuthor Commented:
_agx_, good call, you're right!
0
 
KarlisBConnect With a Mentor Commented:
another thing, if you noticed.

IN() is equivalent to  =ANY(array)
if you understand what array means and how it works, you should understand and know
using in() drawbacks and why it should be avoided with large set of values. in those cases left join is faster and filter by using  where or on clause [leftsided value] is null  or not null depending what you want.

for example, you have two large tables, with 30k records in both
select *
from table_1
where value2 in (select [somevalue] from t2 where (some condition filtering  half of tableas a result))

would be terrible slow compared to this:

select *
from table_1 as T1
where exists(select id from table_2 where value5=T1.value2 )
example above will be working 100X faster for this case.

well, you could do this way too

select * from
(select * from table_1)T1
left join
(select * from table_2)T2
on (T1.value2=T2.value5)
where value5
                      is not null  -- will return all table_1 rows where value5 found and assigned
                      is null  -- will return all table_1 rows where value5 was not found, quite usefull sometimes

this last example will work at about same speed or even a bit faster than second one.

hope this helps.
my english is terible, dont know how to explain in words ... :) sorry
0
 
MFredinAuthor Commented:
Thanks to everyone who helped with this!  Big help, I learned a lot!
0
All Courses

From novice to tech pro — start learning today.