Skip to content

Posts from the ‘MySQL’ Category

24
Mar

MySQL – Steps to break down large time series data & queries (Part 2/2)

database1) Appends date time to the table name

For example, a table name is Data_Connections_History. Then the application appends the date at the end of table name, such as Data_Connections_History_2009_3_14. The application should be able to create these tables automatically and insert the new entries. Read more »

Advertisements
20
Mar

MySQL – Steps to break down large time series data & queries (Part 1/2)

 databaseIn my previous article, I have experimented and optimized query using range data. In this article, I will discuss two approaches to improve the scalability and the performance of continuous large time series data. The scalability is achieved by first separating the time series data into multiple tables in fixed time span and the performance (also scalability) is improved by breaking a long range query into multiple short fix time range queries to take advantage of the query cache. In fact, this is quite a common approach on optimizing time series data. Read more »

23
Jan

MySQL – optimize your query to be more scalable (Part 2/2)

databaseExperiment 3

The next experiment is to discover whether using the combination index can speed up the query. The following indices are created:

CREATE INDEX Id on T1 (Id);
CREATE INDEX Time_Id on T2 (Time, Id); Read more »

23
Jan

MySQL – optimize your query to be more scalable (Part 1/2)

databaseIntroduction

When we design and develop a database application, sometimes scalability is not considered or not a priority. When the size of data grows significantly, we often increase the hardware as the defacto solution. By understanding your MySQL queries, there may be easy steps to free up some processing power. In this article, I am going to discuss step by step, how to improve the performance of a simple MySQL query with a semi-large data set. The example shows a speedup from over 8 mins to just less than 30 secs.

Read more »