1

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!

Improve this question
1
  • 1
    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, 2013 at 20:48

2 Answers 2

Reset to default
3

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.

Improve this answer
3
  • 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, 2012 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, 2012 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, 2012 at 14:19
0

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.

Improve this answer
2
  • 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, 2012 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, 2012 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.