Many places have stated before that optimizing a database through Coildfusion isn't possible. However they are wrong. In this tutorial I'll give you the code to do it in both Coldfusion 8 and Previous Versions.

The SQL Script found herein can easily be modified to be suited for SQL Server as well, as in later versions of both MySQL and SQL Server the syntax for both is becoming more and more standardized across both platforms.

Coldfusion 8 Version (Using CFDBINFO tag & CFQUERY):

<cfdbinfo datasource="#request.dsn#"</p> <div class="richText"> type="tables" name="mydbtables"><br /><cfset tableslist = valuelist(mydbtables.table_name)><br /><cfquery name="optimize" datasource="#request.dsn#" username="#request.user#" password="#request.pass#"><br />OPTIMIZE TABLE #tableslist#<br /></cfquery>

ColdFusion 6 and 7 version (Using CFQuery):

assumes your mysql db name is MYDATABASE. replace in TABLES_IN_MYDATABASE in <cfset> below with your actual db name!

<cfquery datasource="#request.dsn#" username="#request.user#" password="#request.pass#" name="myset2"><br />SHOW FULL tables<br /></cfquery><br /><cfset tableslist = valuelist(myset2.TABLES_IN_MYDATABASE)><br /><cfquery name="optimize" datasource="#request.dsn#" username="#request.user#" password="#request.pass#"><br />OPTIMIZE TABLE #tableslist#<br /></cfquery><br />

Using the above Code Snipplets you can optimize your MySQL (or with Modification your SQL Server) Databases.