I am trying to optimize my query therefore join from low-cardinality index to high-cardinality index.
On my table called "rents" I've executed
show index from rents;
which tells me that column_name "rent_date" has cardinality of 724. Rather when I execute:
select
user_id,
kickscooter_id,
start_time,
rent_date
from rents
group by rent_date;
It returns 300 rows
From what I understand cardinality of index refers to number of unique values according to http://www.mysqltutorial.org/mysql-index/mysql-show-indexes/
From the resource I've mentioned above it says it gives an estimate. How does it estimate the cardinality and how can mine be off by such large number?
Query that I want to optimize:
select
r.user_id,
k.id as kickscooter_id,
st_astext(k.location) as location,
k.created_at,
k.serial_number,
k_st.serial_number as states_serial_number,
st_astext(k_st.gps) as gps_location,
k_st.gps_updated_at,
r.start_time,
r.end_time
from kickscooters k
Straight_Join kickscooter_states_190614 k_st
on k.serial_number = k_st.serial_number
Straight_Join rents r
on k.id= r.kickscooter_id
;
when I r.kickscooter_id is set as non_unique index with cardinality of 10395,
k.id is primary key with cardinality 4636
k.serial_number has two index: one as unique and other as non-unique, both have cardinality of 4636.
k_st.serial_number in non-unique and has cardinality of 4162
