Menu
Evening,
I'm going through the long process of importing data from a battered, 15-year-old, read-only data format into MySQL to build some smaller statistical tables from it.
The largest table I have built before was (I think) 32 million rows, but I didn't expect it to get that big and was really straining MySQL.
The table will look like this:
surname name year rel bco bplace rco rplace
Jones David 1812 head Lond Soho Shop Shewsbury
So, small ints and varchars.
Could anyone offer advice on how to get this to workas quickly as possible? Would indexes on any of the coulmns help, or would they just slow queries down.
Much of the data in each column will be duplicated many times. Some fields don't have much more than about 100 different possible values.
The main columns I will be querying the table on are: surname, name, rco, rplace.
316
Can MySQL handle 100 million records? Yeah, it can handle billions of records. If you properly index tables, they fit in memory and your queries are written properly then it shouldn't be an issue.
Sure, and a whole lot more. I've personally worked with single tables in MySQL that had ten billion records. They were, needless-to-say, very carefully partitioned and had very simple access patterns, but they performed just fine as long as application developers tuned their query patterns Very Carefully.
The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
INDEX on a column fastens the search.
Try to INDEX columns that you would be using more often in queries. As you have mentioned you would be using the columns surname, name, rco, rplace. I'd suggest you index them.
Since the table has 32 million records, indexing would take sometime however it is worth the wait.
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
