# MySQL table design / architecture, table is too big

I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table.

I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in the table structure made it too big, I can't predict the size of all the columns so I'm using varchar\text\medium text for most of the fields. when I insert all the data to the DB I monitor the errors and when I see that a certain field is too small for the data I'm trying to insert I'm increasing the size of the field (e.g. from varchar(1000) to varchar(2000)).

until now I have about 1.8M~ rows, I think that I'm doing something wrong.

here is the structure of my table -

CREATE TABLE PT (
patID int(11) NOT NULL,
Title varchar(450) DEFAULT NULL,
IssueDate date DEFAULT NULL,
NoFullText tinyint(1) DEFAULT NULL,
Abstract text,
ForeignReferences varchar(15000) DEFAULT NULL,
CurrentUSClass varchar(2200) DEFAULT NULL,
OtherReferences mediumtext,
ForeignPrio varchar(900) DEFAULT NULL,
CurrentIntlClass varchar(3000) DEFAULT NULL,
AppNum varchar(45) DEFAULT NULL,
AppDate date DEFAULT NULL,
Assignee varchar(300) DEFAULT NULL,
Inventors varchar(1500) DEFAULT NULL,
RelatedUSAppData text,
PrimaryExaminer varchar(100) DEFAULT NULL,
AssistantExaminer varchar(100) DEFAULT NULL,
AttorneyOrAgent varchar(300) DEFAULT NULL,
ReferencedBy text,
AssigneeName varchar(150) DEFAULT NULL,
AssigneeState varchar(80) DEFAULT NULL,
AssigneeCity varchar(150) DEFAULT NULL,
InventorsName varchar(800) DEFAULT NULL,
InventorsState varchar(300) DEFAULT NULL,
InventorsCity varchar(800) DEFAULT NULL,
Claims mediumtext,
Description mediumtext,
InsertionTime datetime NOT NULL,
LastUpdatedOn datetime NOT NULL,
PRIMARY KEY (patID),
UNIQUE KEY patID_UNIQUE (patID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


what should I do? I have about 20% of the data (which means I'm going to need 350GB~ space) what is the performance impact here? should I divide the table into several tables over several HDs? I'm going to use sphinx to index and query the data in the end.

should I partition the table and use another disk for the partitioned table? will it help to create several tables out of this one? what would be the best way of doing so?

most of the queries will be SELECT, I'm going to search on about 4-5 text columns in this table (full text search + Sphinx).

Thanks!

-
Smells like it shuld not be one table. I see a lot of information that should go out into - you know - a RELATIONAL construct. –  TomTom Jun 2 '13 at 20:48

Well first there is no way that all of this should ever be in one table. You need related tables for information such as Assignee and Inventor. Please read about normalization.

But in reality are you sure that mysql is the way to go for this since you seem mostly to want to search on data that is not always in the same form. Personally, for this type of thing I would look at NoSQL databases for the text parts in conjuction with a relational database for the data that is easy to describe and determine the size of.

-
even after creating those tables, it doesn't really changes my question, I can create another table for the information I'm not searching on. So I'll have 2-3 more tables, I still need to deal with my text vs. varchar and the capacity of the DB\HD. I'm using windows. how would NoSQL help me here? –  YSY Jul 12 '12 at 14:16
If you create the tables and store the values once and create them with a surrogate key, you will save space. –  HLGEM Jul 12 '12 at 14:18
you're right, but this is hardly 6%-10% of the entire DB. I can't even alter the tables now, mysql copies the MYD file and I don't have enough space for both the tmp and the live data file. –  YSY Jul 12 '12 at 14:19

A quick calculation gives an average usage of ~55KB per record of data (1.8M rows at 100GB, ignoring any indexes). Is this reasonable for the amount of text you are grabbing? If yes, then you will need the calculated space anyways. It'll just break down to how you will organize the data.

An alternative would be to externalize the large chunks of text into files and just put a link to them in the database. I don't know how you will build up your fulltext search index and which data you need to access, but maybe you can just throw the file's contents to the indexing engine. Finally, you will end up using the same amount of space on disk ... and might have 9M rows times 3 or 4 files on disk instead of one large database. The files could go on a separate file server.

-
the Claims and Description fields can both carry large text chunks (up to 1MB~ per record). the Abstract field can carry about 200kb per record. I thought that the text fields are stored outside the MYD file, but I can't find them. I don't see how we managed to get this big if the text fields located in a different path. as for now I can't alter the table because I don't have enough disk space on my drive (alter creates a tmp copy). I wanted to see how changing the ForeignReferences field from varchar to text would affect the size of the MYD file. –  YSY Jul 13 '12 at 10:36
Values in TEXT columns are not stored in the row buffer, but nonetheless get stored in the database at some other memory location (depending on storage engine). Including TEXT fields in your queries might give you performance problems (see: stackoverflow.com/questions/2883867/…) –  MicSim Jul 13 '12 at 11:19