Page 133 - ASBIRES-2017_Preceedings

INCREASING THE SPEED OF SEARCHING PROCESS IN MYSQL DATABASE FOR DYNAMIC DATA
The Figure 2 shows the average search
times taken by each and every action source
where the slowest action source which slow
down the system when searching can be
identified.
Figure 2: Graph indicating the average
search time of action sources
The Figure 3 indicates the search times
for the products available where the product
which gives results in the lowest search time
can be identified.
Figure 3 : Graph indicating the average
search time of products
5 RESULTS AND DISCUSSION
The Elastic search configurations were
up and running successfully in the testing
environment after some effort.
That could be considered as a successful
implementation because the testing had
proven the stability of the migration of the
databases in the testing environment.
Therefore, after the client’s confirmation the
changes can also be released to the live
production environment.
The storage issue was resolved by the
data cubes. This part of the research was not
communicated properly with the client
Therefore; this would be an additional
development for the enhancement of
123
resource utilization. Since the developments
are not strictly based on the domain, this
solution could be applied commonly for
other projects as well with suitable
modifications. Creating the data cubes was a
challenge without using any tools and
depending on only the basic concept of data
cubes. That would be an advantage in the
future since the solution is not depending on
any framework or utility. As further
improvements to improve this Business
Intelligence part the database could be
scheduled to keep statistical data according
to the following structure:
Keep 3 weeks of row data
This is to trace reasons for any recent errors
recorded in statistical data.
After 3 weeks, keep 2 weeks of data
calculated per minute.
According to this, the data which is older
than 3 weeks will be reduced into per
minute average data and the row data will be
deleted.
Keep hourly data for one month from that
point.
Data older than 5 weeks will be
averaged into hourly data and the rest will
be deleted. After a month, keep daily data
for 3 months After 3 months, keep weekly
data for 1 year Keep monthly data from 1
year onwards .Furthermore, these time
periods need to be analyzed according to the
client requirements to be relevant to their
usage patterns. According to this the storage
savings with the suggested solution will be
as follows:
 For 3 weeks keep original data (No
compression)
 For next 2 weeks (per minute)
60 * 24 * 14 = 20160 rows
 For next month (per hour)
24 * 30 = 720 rows
 For next three months (per day)
30 * 3 = 90 rows
 For next one year (per week)
4 * 12 = 48 rows
 For another 5 years (per month)
12 * 5 = 60 row