Inspecting Primary And Secondary Index Key Utilization For MySQL 5.7.32 In Lucee CFML 5.3.7.47

SELECT
	key_column
FROM
	my_table
ORDER BY
	key_column DESC
LIMIT
	1

<cfscript>

	// In this query, we're going to be inspect the numeric PRIMARY KEY columns to see
	// how much wiggle-room they have in terms of INT storage space.
	```
	<cfquery name="stats">
		SELECT
			t.table_name,
			t.table_rows,
			/*
				CAUTION: No all primary keys are auto incrementing values. On tables in
				which the primary key is a foreign key reference, this column will be
				returned as an empty value.
			*/
			t.auto_increment,
			s.column_name,
			s.cardinality,
			c.data_type,
			c.column_type,
			IF( c.column_type LIKE '%unsigned%', 4294967295, 2147483647 ) AS max_possible_value,

			/* These will be calculated in the ColdFusion code. */
			( 0 ) AS current_value,
			( 0 ) AS percent_used
		FROM
			information_schema.TABLES t
		INNER JOIN
			information_schema.STATISTICS s
		ON
			(
					t.table_schema = 'invisionapp'
				AND
					s.table_schema = t.table_schema
				AND
					s.table_name = t.table_name
				AND
				 	/*
				 		We only care about the PRIMARY KEY constraint in this particular
				 		query. This is not the only column that can run out of space -
				 		we'll look at other columns after this.
				 	*/
					s.index_name = 'PRIMARY'
				AND
					/* 
						Some tables have compound keys - that is, a non-synthetic primary
						key that is composed of multiple columns. For the sake of this
						experiment, we're going to assume that the first column is the
						one that is likely to run out of space. As such, we're going to
						limit the results to columns that are the FIRST column in the
						sequence of index-columns.
					*/
					s.seq_in_index = 1
			)
		INNER JOIN
			information_schema.COLUMNS c
		ON
			(
					c.table_schema = t.table_schema
				AND
					c.table_name = t.table_name
				AND
					c.column_name = s.column_name
				AND
					/*
						We only care about INT vs. UNSIGNED INT in this particular query.
						While there are other types of numeric values, those are less
						likely to cause a problem for us.
					*/
					c.data_type = 'int'
			)
		ORDER BY
			t.table_name ASC
		;
	</cfquery>
	```

	// Now that we have the general stats on the primary keys, let's inspect each table
	// and get its current primary key value.
	// --
	// NOTE: For tables that are using AUTO_INCREMENT column definitions, we could have
	// used the "t.auto_increment" value in the above query. It's cache statistics; but,
	// it would be close enough (cache lasts 24-hours according to MySQL docs).
	loop query = stats {

		// Since we know that this query contains the PRIMARY KEY columns, we know that
		// we can efficiently get the MAX value by ordering the column DESC with a LIMIT
		// of 1 without doing a full-table scan.
		```
		<cfquery name="pkey">
			SELECT
				( #stats.column_name# ) AS value
			FROM
				#stats.table_name#
			ORDER BY
				#stats.column_name# DESC
			LIMIT
				1
		</cfquery>
		```

		stats.current_value[ stats.currentRow ] = val( pkey.value );
		// Round-up for max possible FEAR value.
		stats.percent_used[ stats.currentRow ] = ceiling( stats.current_value / stats.max_possible_value * 100 );

	}

	stats.sort( "percent_used, table_name, column_name", "desc, asc, asc" );
	dump( stats );

</cfscript>

<cfscript>

	// In this query, we're going to be inspect the numeric SECONDARY KEY columns to see
	// how much wiggle-room they have in terms of INT storage space.
	```
	<cfquery name="stats">
		SELECT
			t.table_name,
			t.table_rows,
			s.column_name,
			s.cardinality,
			c.data_type,
			c.column_type,
			IF( c.column_type LIKE '%unsigned%', 4294967295, 2147483647 ) AS max_possible_value,

			/* These will be calculated in the ColdFusion code. */
			( 0 ) AS current_value,
			( 0 ) AS percent_used
		FROM
			information_schema.TABLES t
		INNER JOIN
			information_schema.STATISTICS s
		ON
			(
					t.table_schema = 'invisionapp'
				AND
					s.table_schema = t.table_schema
				AND
					s.table_name = t.table_name
				AND
				 	/*
				 		We only care about the secondary column constraints in this
				 		particular query.
				 	*/
					s.index_name != 'PRIMARY'
				AND
					/* 
						Many secondary indices use a compound key - a key composed of
						multiple columns. Again, in order to keep things easy, we're
						going to limit our scope to the first column in each index so
						that we can efficiently gather the max value in the CFML.
					*/
					s.seq_in_index = 1
			)
		INNER JOIN
			information_schema.COLUMNS c
		ON
			(
					c.table_schema = t.table_schema
				AND
					c.table_name = t.table_name
				AND
					c.column_name = s.column_name
				AND
					/*
						We only care about INT vs. UNSIGNED INT in this particular query.
						While there are other types of numeric values, those are less
						likely to cause a problem for us.
					*/
					c.data_type = 'int'
			)
		ORDER BY
			t.table_name ASC
		;
	</cfquery>
	```

	// Now that we have the general stats on the secondary keys, let's inspect each table
	// and get its current secondary key value.
	loop query = stats {

		// Since we know that this query contains columns that are the first column in an
		// index sequence, we know that we can efficiently get the MAX value by ordering
		// the column DESC with a LIMIT of 1 without doing a full-table scan.
		```
		<cfquery name="pkey">
			SELECT
				( #stats.column_name# ) AS value
			FROM
				#stats.table_name#
			ORDER BY
				#stats.column_name# DESC
			LIMIT
				1
		</cfquery>
		```

		stats.current_value[ stats.currentRow ] = val( pkey.value );
		// Round-up for max possible FEAR value.
		stats.percent_used[ stats.currentRow ] = ceiling( stats.current_value / stats.max_possible_value * 100 );

	}

	stats.sort( "percent_used, table_name, column_name", "desc, asc, asc" );
	dump( stats );

</cfscript>