Designing optimal indexes for distributed databases requires a deeper understanding of how distributed databases work and how indexes are split into tablets that reside on different nodes. A basic index can be created using the `CREATE INDEX` statement with three components: partition, clustering, and include. The partition part decides how rows in the index are distributed, the clustering part decides how rows with the same partition column values are ordered, and the include part includes additional columns to avoid a round-trip to the main table. Equality indexes are used for point lookups where we know the exact value of the key, while range and inequality indexes are used for range scans and in-equality comparisons respectively. Index-only scans can be achieved by adding columns needed for the query directly to the index. Cardinality of columns should be considered when designing indexes to avoid low cardinality columns like boolean(T/F) or days of week. Partial indexes can also be created to index only specific patterns, improving read performance and reducing write overhead.