Clear Filters
Clear Filters

MATLAB selecting rows from a MySQL database is very slow

7 views (last 30 days)
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.

Answers (1)

Harsh Mahalwar
Harsh Mahalwar on 16 Feb 2024
Edited: Harsh Mahalwar on 16 Feb 2024
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,
% 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:
I hope this helps, thanks!

Tags

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!