I have a MySQL database that contains over 100 tables. This database is used by a CMS and every 6 hours, a PHP script uses all these tables with a lot of logic, calculations and SQL joins and generates a large dataset with over 1 million rows and inserts it into a table that we call "deals". This is basically a table with 50 columns and a lot of optimized indexes and each row has the same stuff, something like this:
dealid, price, productname, productcolor, productdimensions, onstock, brand, etc. This "deals" table is then used by many other front- and backend applications for all kinds of purposes. Examples are: a page on which a customer can filter and then see results. A page on which a customer can find information about a deal. An XML feed that is used by affiliates that contains all products of brand X (+/- 150,000 rows from the 1,000,000 rows table). An iOS app that is used to show you what kind of accessoires there are available for your iPhone. All these applications use the same 1,000,000 row MySQL table to easily find and display data. These applications are experiencing speed issues as the "deals" table grows. MySQL seems to have a lot of trouble handling these kind of numbers. I would not expect that to be the case but perhaps having only one MySQL server with 32GB RAM and 8 cores is a problem. It's not an option to upgrade to a couple of servers with 128GB RAM and also I'm not really convinced that this would actually solve the problem. I've made a little PHP script that, besides insert the 1,000,000 rows into MySQL, also indexes them in elasticsearch. Then, I tested both the MySQL table and the ES index in a simple sandbox filtering application. The speed of the ES based application was absolutely amazing compared to the MySQL based application. So it looks like speed-wise, ES wins. So.. is this the right usage of an ES instance? Or am I incorrect thinking ES would be the best solution for my MySQL speed issue? -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
