Looks pretty good to me. Just thinking loud.
Wondering if when you update something in MySQL from your application, You should not compute the document you want to index an simply update elasticsearch with this new info. It would save heavy read operations every 6 hours in MySql. And you would offer real time search. That's basically what I was doing at my former job. David > Le 7 déc. 2014 à 19:16, Marcel emblazoned <[email protected]> a écrit : > > 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/553ED908-A533-492F-B4D3-3AD5069E8C6A%40pilato.fr. For more options, visit https://groups.google.com/d/optout.
