Not indexing all table rows?

[zq29]

I have a table that contains 1,679,537 rows (UK postcodes) which also has a field that holds a float value of the distance of each postcode from another specific postcode. I am trying to index this distance field to help speed up searches, after indexing this field it's count is only 279,922 indexes.

 

I'm not 100% sure how indexing works internally, does it only index distinct values? If so, there are 280,801 distinct distances so that doesn't quite add up.

 

Before indexing, my queries return rows, but after indexing I'm getting nothing returned. Am I using indexing properly?

 

Here's one of my queries, the only thing that changes is the distance range.

SELECT COUNT(*) FROM `club` AS c, `postcodes` AS p WHERE c.`postcode` = p.`postcode` AND p.`dist` BETWEEN '0' AND '5'

[Illusion]

Is there any purpose of using single quotes for 0 and 1 in your query.

 

 

All storage engines have a total index length of at least 256 bytes. Most storage engines have higher limits.

 

 

 

[bubblegum.anarchy]

Though this has nothing to do, directly, with your actual question, SA - would adding an additional indexed enum column named `range` with values such as 0-5, 5-10, 10-5 (depending on application) improve your query performance?

[fenway]

You may benefit from selecting all the of the p records first, then joining is this table as a derived table.