Is this the right usage for elasticsearch?

Elastic Stack Elasticsearch
1

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.
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.

2

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 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.
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 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.
For more options, visit https://groups.google.com/d/optout.

3

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 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.
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 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.
For more options, visit https://groups.google.com/d/optout.