Multiple-filters real-time implementation with MySQL
<p>I'm developing a PHP application for real-estate portal, and I have MySQL database with Properties table. This table has about 500,000 rows and about 20 columns for property features. Assume that each feature is an integer.</p>

<p>Examples of features:</p>

<ul>
<li>number of rooms in the apartment (1-10)</li>
<li>type of building (1-20)</li>
<li>condition of building (1-10)</li>
</ul>

<p>I need to implement a web page with a multi-filter real-time navigation panel. The idea is that users can select several features to be equal to particular values.
For example: 1-2 rooms ("1","2"), building is of 196x ("6"), state is average, good or excellent ("3","4","5"), in particular City.</p>

<p>The key requirement is the ability for users to see the number of matching properties near each feature filter, taking into account the currently selected filters.</p>

<p>This is the example how it should look like:</p>

<pre><code>Rooms: 1[X], 2[X], 3[ ] (15000 more), 4[ ] (10000 more)
State: bad[ ] (1000 more), average[X], excellent[X]
Year: 1950[ ] (19000), 1960[ ] (20000), 1970[ ] (18000)
City: A[ ] (25000), B[ ] (18000), C[ ] (30000)
Price: &lt;100000[ ] (20000), &gt;100000[ ] (30000)
</code></pre>

<p>The "[ ]" stands for empty checkbox (i.e. filter value is not selected), and the "[X]" stands for checked checkbox (i.e. filter value is selected).</p>

<p>When user selects a particular feature value, lets say City=London, the numbers should change because they are now limited by the previous selections:</p>

<pre><code>Rooms: 1[X], 2[X], 3[ ] (5000 more), 4[ ] (5000 more)
State: bad (1000 more), average[X], excellent[X]
Year: 1950 (19000), 1960 (20000), 1970 (18000)
City: A[X], B (+4000), C (+3000)
Price: &lt;100000 (5000), &gt;100000 (6000)
</code></pre>

<p>I have tried to use the following SQL query for each feature (X):</p>

<pre><code>SELECT FeatureX, COUNT(*) AS num FROM properties WHERE selectedFeature1=Val1 AND selectedFeature2=Val2 ... AND selectedFeatureN=ValN GROUP BY featureX;
</code></pre>

<p>However a set of these queries take several seconds, and I need this to work in real-time i.e &lt;200 ms on backend.</p>

<p>I have tried to keep the whole table in shared memory, but unserialize() of an array with 500,000 records also takes about 1s.</p>

<p>The only solution I found is to develop a standalone application (for example in NodeJS) which keeps data in memory as an array of objects and provides API to the main PHP application. This app refreshes data from MySQL database every minute.</p>

<p>However before starting to implement this I would like to ask if there is any solution based on MYSQL itself for this kind of task?</p>

<p>If not, is there a purely PHP solution?</p>

<p>Do you have any generic recommendations how to approach for this kind of task?</p>