Congrats and welcome to ES. I have a similar application, a library catalog, which has to be searched and reindexed daily.
As David said, you should add a component to your app to stream updates over a timeline, to reduce the volume to reindex. Jörg On 12/7/14, Marcel emblazoned <[email protected]> wrote: > > > 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. > -- 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/CAKdsXoE5cOCqQAUr18yyaGLoFhPDghz%3Dv0LjhPvqpCgbrCh-Cw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
