a slow query using MAX() vs a much faster version using ORDER BY and LIMIT 1

improved_mySQL_max.cfm

		
		<!--- very slow once trying to get the MAX of over 200K records; presumably, 
		using MAX on varchar(40) that had to be CAST AS UNSIGNED was not performing well, 
		but I don't know the underlying reason; note adding an INDEX or not on ExternalItemID 
		did not make a noticeable difference --->
		<cfquery name="local.rsGetMaxExternalItemID"
				datasource="#variables.settings.DSN_master#"
				timeout="#variables.settings.databaseTimeout#">
			SELECT		MAX( CAST(ExternalItemID AS UNSIGNED) ) AS ExternalItemID
			FROM		StreamItems
			WHERE		UserID = <cfqueryparam value="#arguments.UserID#" cfsqltype="cf_sql_integer" />
			AND		FilterID = <cfqueryparam value="#arguments.FilterID#" cfsqltype="cf_sql_integer" />
			AND		Source = <cfqueryparam value="#arguments.Source#" cfsqltype="cf_sql_varchar" />
		</cfquery>


		<!--- mere milliseconds, even with over 200K records while under mild load 
		(competing INSERTs on the table) --->
		<cfquery name="local.rsGetMaxExternalItemID"
				datasource="#variables.settings.DSN_master#"
				timeout="#variables.settings.databaseTimeout#">
			SELECT		ExternalItemID
			FROM		StreamItems
			WHERE		UserID = <cfqueryparam value="#arguments.UserID#" cfsqltype="cf_sql_integer" />
			AND		FilterID = <cfqueryparam value="#arguments.FilterID#" cfsqltype="cf_sql_integer" />
			AND		Source = <cfqueryparam value="#arguments.Source#" cfsqltype="cf_sql_varchar" />
			ORDER BY	StreamItemID DESC
			LIMIT 1
		</cfquery>