So, I have a table in mysql that stores user interests, initially it was just data to analyze when needed. The users_interests table is a table that stores the user_id, the section_id, the location_id, the property_type_id, the minimum price and the maximum price. This table now contains about 6 million records, and now we need to analyze it in real time, but mysql is not helping (very slow and sometimes timeout).... What I need to achieve is building a form that has many filters (that are optional) with which the user can filter (Every time the user chooses a filter it will send an api to the database to count the unique number of users that have interest with that criteria).
The form should contain fields for section_id, property_type_id, location_id, min_price, max_price and country code.... And each time the user chooses a filter for example section_id = 1, it should send an api to count unique users in real time that have interests in section_id = 1 and respond in less than a second optimally, then when the user select property_type_id = 4 then we will send another api to count unique users in real time that have interest in section_id = 1 and property_type_id = 4 and etc as the user keeps filtering.
As I mentioned, I tried with mysql and it was really slow and sometimes would timeout (even with indexing, I tried also composite indexing but they won't work as all of my filters are optional) ... I read into this problem, and a lot of people mentioned the hyperloglog algorithm, which what I gathered approximates the distinct count to optimize performance. So are there big data solutions that support the hyperloglog algorithm? And are they sufficient to help me solve my problem?