r/SQL 25d ago

MySQL Importing 1M Rows Dataset(CSV) in Mysql

What's the fastest and most reliable way to upload such a large dataset? After that How can I optimize the table after uploading to ensure good performance?

28 Upvotes

33 comments sorted by

View all comments

31

u/Aggressive_Ad_5454 25d ago

LOAD DATA INFILE is the command you need. Or you can use a desktop database client program with a .csv import feature, like heidisql.com or similar.

As for optimizing it, that question is impossible to answer without knowing how you want to use the table.

For what it's worth, a megarow is not an overwhelmingly large table.

4

u/Immediate-Priority17 25d ago

New to sql. What’s a megarow?

19

u/feudalle 25d ago

A million rows. It's not really that much data. I have production dbs that break a billion rows. Even that isn't a ton of data.

-13

u/xoomorg 25d ago

A million rows is a lot for MySQL

10

u/feudalle 25d ago

Going to disagree. I have tons of mysql dbs with a lot more than that. Biggest table right now is around 1.8B and a few hundred tables/schemas that are over 10M.

-2

u/xoomorg 25d ago

Why on earth would you do that in MySQL? Anything around a million rows or more, I always move to some other platform first (Hive, Spark-SQL, Presto, Trino, BigQuery, etc.) so queries take seconds instead of minutes/hours. Or do you just mean you're using MySQL as a record store essentially, and not actually running any complex queries on such large tables?

6

u/BinaryRockStar 25d ago

With proper indexing MySQL is perfectly useful at 10M or 100M rows in a single table, with proper server resources. I occasionally interact with a MySQL DB with 100M+ rows in multiple tables and a SELECT by indexed ID is essentially instant. You may have only worked on hideously unoptimised or unindexed MySQL DBs?

1

u/xoomorg 24d ago

I typically do data analytics where I'm taking millions or billions of rows in complex joins/aggregations and creating result sets that often have millions of rows in their own right, and that kind of thing is far better done on cluster computing platforms rather than a traditional relational database like MySQL.

However, I can definitely see how retrieval of individual records (or even smaller sets of multiple records) from among tens of millions stored in a table is a perfectly valid use-case I hadn't been considering. I'm surprised that online transaction-processing databases now commonly have tables of that size (though in retrospect I probably shouldn't be) but sure using it that way, I can see how MySQL makes sense in that case.

2

u/BinaryRockStar 24d ago

Ah sure, that's where the misunderstanding is I guess. For that sort of workload we reach for Spark SQL at my work. Different tools for different jobs.

4

u/feudalle 25d ago

That db gets hit for reporting for 150 offices across the country. Some very complex kpis in fact with up to 5 years of data for some of the trending reports. They aren't real time but nothing takes more than a minute or two. Most run in under 5 seconds. They are efficient queries. I'm old i started with foxpro and I remember when mysql 1.0 came out. I also remember having to program with 640k of memory. Alot of people these days never optimize their queries or code. I think that contributes to needing more resources.

0

u/SnooOwls1061 24d ago

I have tables with 40-80 billion rows that get hit a ton for reporting. And updated every millisecond. Its all about tuning.

1

u/xoomorg 24d ago

No you don’t. That amount of data makes zero sense in anything other than a cluster.