I'm checking out whether InfiniDb is the right DB to use for developing an analytic Web-Application (PHP). The main use is to read and analyze data which is organized into star schemas.
Apart from some smaller drawbacks in terms of flexibility in the use of SQL (between joins not supported), we currently have performance issues with simple queries (one table, simple filter, no join, <10.000 rows). They take about 0,5 seconds while with myISAM tables they take about 0,02 seconds... I know that the first time data is read for a query, it's reading it from disk and caching the related data into memory.
The problem with server-side web-applications seems to be that the data is read from the disk for caching over and over again, as the web-application typically creates a new connection for every request. I did not expect this caching to be dependent of the connection as I thought this would be managed on the server level.
To be sure I also tested these simple queries directly on the server and as expected they take ~0,5 seconds the first time (caching) and ~0,01 seconds for the following executions, so once cached the performance is good.
Is there a solution for this problem? Response time is key for the user acceptance of any web-app.
Thanks in advance,