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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

_agx_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MFredinAuthor Commented:
_agx_, good call, you're right!
0
KarlisBCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.