MATLAB selecting rows from a MySQL database is very slow

Hi,

I have a MySQL database on my local PC. I run a select in the MySQL workbench and it fetches 107,136 rows in 7.75 sec.

MATLAB executing the same query ran for 684 sec. Running the query in the Database Explorer and importing the data is also slooooow.

It is a simple query. SELECT Name, Time_Period, Run_ID, Study_Info FROM test_augmthlya.ZoneHour1 WHERE Report_Year < 2024 and Report_Month = 1;

When we run using SQL Server this query is really quick in that we never thought abou the time it took.

THanks for your advice and suggestions.

Answer 1

[Harsh Mahalwar]

Hi Bruce,

As I can understand, you’re trying to fetch a large dataset into MATLAB and currently, it is taking around 11 minutes for MATLAB to fetch it, compared to 7.75 seconds it took by MySQL workbench.

Here’s a workaround, you can try using the “fetch” function from MATLAB database toolbox.

For this example, I am using MATLAB R2023b and I have created a dummy dataset with 163160 rows (I am using MySQL server on my local machine),

% Create a database connection to the ODBC data source |conn|.
% Specify the user name and password.

datasource = "conn"
username = "root"
password = "admin"
conn = database(datasource,username,password)

Feel free to use this link to learn more on creating a database connection,

Create ODBC database connection - MATLAB odbc - MathWorks

% This sql query helps us to retrieve all the rows from the world.city table
sqlquery = 'select Name, CountryCode, District, Population from world.city';

% Here, I have used tic-toc to calculate the elasped time.
tic
% dataArray stores the data from the "fetch" function.
dataArray = fetch(conn, sqlquery)
toc

Using MATLAB’s “fetch” function I was able to fetch all the rows in 1.76 seconds!

You can learn more about the ”fetch” function by going through the following link:

Import data into MATLAB workspace from execution of SQL statement - MATLAB fetch - MathWorks

I hope this helps, thanks!