I have a 'joinings' table which lists the joining date of all the employees along with their office location, department no and the job advertisement through which they were selected. I want to be able to query this table by their joining dates and group them using one of the other columns. I also want to be able to apply a where clause on any of the columns.
It could be like this -
SELECT month(joining_date) as month, count(*) as entries , branch_name FROM joinings WHERE joining_date >= '2017-01-01' AND joining_date <= '2017-05-31' AND department_no in (5,4,7) AND source_of_appliction IN ('glassdoor', 'linkedin') GROUP BY month(joining_date), branch_name
OR could be like this -
SELECT year(joining_date) as month, count(*) as entries , source_of_application FROM joinings WHERE joining_date >= '2017-01-01' AND joining_date <= '2019-12-31' AND department_no IN (5,4,7) AND branch_name IN ('ZEIT','DUS') GROUP BY year(joining_date), source_of_application
This table can contain thousands of records for several years. The range of the joining date that I would query could be between a single month, range of months or range of years.
I would like to know what indexes need to be created to get me optimized performance for my select queries. If not the exact indexes, I am at least looking out for pointers to get me started to create the right indices.
What I have-
I currently have a multi-column index created on
joinings(joining_date, source_of_application) and also have individual indexes for all the single columns. But my select performs a full table scan for the queries that I have listed above.