So before we continue with the Phalcon integration lets have a little fun. Our current system makes use of a Mysql db soon to be a Maria db for its transactional database. Now we are starting to reach 60 million records in certain tables.
Now I tell you one thing even with the beast of the server we have when you start text searching on 60 million records things go haywire.
So how do we resolve this issue?
After a developer in our team wasted many hours looking at different offerings we decided on using Elastic Search now called Elastic. I was a little unsure at first but after watching the video of Netflix's integration using Elastic I was sold. Of course you cant go by what other people say because every ones system is different. So I did my own test on my local system and here are the results.
Full phrase search on my local mysql on 500 000 records 30-50 seconds.
Full phrase search on my local elastic on 500 000 records 0.02 seconds no variation.
Okay now I am totally sold so how do we make use of this awesome system?
To start with we consolidate all our financial tables into one index(type of table in elastic) and now where we used to have a long statement with many inner joins that took ages to run and added load to our database we now have a 10 second pull from Elastic.
This is just one application we have many others that will soon be implemented.
In my next post I will go deeper into the setting up of Elastic setup and the PHP query driver.