I have a table called FANS that stores demographic information on various people including name, address and DOB. The table itself contains 7,655 rows of data. I have created a single INDEX on the CITY column. I know for a certainty that there are only 40 unique cities in the table and there are no NULL values. I'm trying to run a simple experiment on when the optimizer begins to use the INDEX.
SELECT CITY FROM FANS WHERE CITY BETWEEN 'APOLLO BEACH' AND 'BRANDON';
I learned previous from MySQL that Selectivity = cardinality / number of records * 100% where cardinality would be the number of unique values in the index.
When I run "Explain Plan" in SQL developer, it returns a cardinality of 500. Thus the correct math would be 500 / 7,655 * 100%?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `FANS` ADD INDEX `fans_idx_city` (`CITY`);
SELECT FANS.CITY FROM FANS WHERE FANS.CITY BETWEEN 'APOLLO BEACH' AND 'BRANDON'