# Is this the right usage for elasticsearch? **URL:** \ **Category:** Elasticsearch\ **Created:** [December 7, 2014, 6:16pm UTC](https://discuss.elastic.co/t/is-this-the-right-usage-for-elasticsearch/21127 "2014-12-07T18:16:05Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Marcel\_emblazoned](https://avatars.discourse-cdn.com/v4/letter/m/35a633/32.png) [@Marcel\_emblazoned](https://discuss.elastic.co/u/Marcel_emblazoned)\ **Post date:** [December 7, 2014, 6:16pm UTC](https://discuss.elastic.co/t/is-this-the-right-usage-for-elasticsearch/21127/1 "2014-12-07T18:16:05Z")
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 [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [December 7, 2014, 6:28pm UTC](https://discuss.elastic.co/t/is-this-the-right-usage-for-elasticsearch/21127/2 "2014-12-07T18:28:55Z")
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 [leider.m@gmail.com](mailto:leider.m@gmail.com) 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 [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com). > For more options, visit [https://groups.google.com/d/optout](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 [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/553ED908-A533-492F-B4D3-3AD5069E8C6A%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/553ED908-A533-492F-B4D3-3AD5069E8C6A%40pilato.fr). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [December 7, 2014, 6:48pm UTC](https://discuss.elastic.co/t/is-this-the-right-usage-for-elasticsearch/21127/3 "2014-12-07T18:48:43Z")
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 [leider.m@gmail.com](mailto:leider.m@gmail.com) 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 [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9e1cf7b6-1b80-4578-8e0e-261a3a914b20%40googlegroups.com). > For more options, visit [https://groups.google.com/d/optout](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 [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoE5cOCqQAUr18yyaGLoFhPDghz%3Dv0LjhPvqpCgbrCh-Cw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoE5cOCqQAUr18yyaGLoFhPDghz%3Dv0LjhPvqpCgbrCh-Cw%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 12:45am UTC](https://discuss.elastic.co/t/is-this-the-right-usage-for-elasticsearch/21127/4 "2017-07-06T00:45:10Z")