<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>Hi David,</div><div><br></div><div>This test was using a "select * from ... limit..." query from the MySQL DB that serves the GBIF data portal.</div><div>The table queried was as below.</div><div><br></div><div>It does not make too much sense to suggest schemas for use with the IPT as the IPT allows for mapping to the DwC terms from arbitrary schemas. However, if someone was designing a table for this purpose then creating a flat table with terms from the DwC would be a good start. In essence the GBIF portal looks similar to that structure, and currently holds 270 million + records. If the DB were being used for multiple collections and normally queried on a single collection at a time, it would make sense to do range partitioning (<a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html">http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html</a>) although you would have to be into many millions of records, or on poor hardware to really notice the benefits. If your queries are spanning all collections, then you would not want to do that either, as performance would degrade; you may consider a different partitioning strategy though if your query patterns allow it. The most important thing I would recommend with databases is to use the smallest types possible (e.g. UNSIGNED SMALL INT instead of INT) - the difference in performance is astronomical as the DB grows, and something we learnt with the GBIF Portal as it grew.</div><div><br></div><div>Hope this helps,</div><div>Tim</div><div><br></div><div>CREATE TABLE `raw_occurrence_record` (</div><div> `id` int(11) NOT NULL auto_increment,</div><div> `data_provider_id` smallint(6) default NULL,</div><div> `data_resource_id` smallint(6) default NULL,</div><div> `resource_access_point_id` smallint(6) default NULL,</div><div> `institution_code` varchar(255) default NULL,</div><div> `collection_code` varchar(255) default NULL,</div><div> `catalogue_number` varchar(255) default NULL,</div><div> `scientific_name` varchar(255) default NULL,</div><div> `author` varchar(255) default NULL,</div><div> `rank` varchar(50) default NULL,</div><div> `kingdom` varchar(150) default NULL,</div><div> `phylum` varchar(150) default NULL,</div><div> `class` varchar(250) default NULL,</div><div> `order_rank` varchar(50) default NULL,</div><div> `family` varchar(250) default NULL,</div><div> `genus` varchar(150) default NULL,</div><div> `species` varchar(150) default NULL,</div><div> `subspecies` varchar(150) default NULL,</div><div> `latitude` varchar(50) default NULL,</div><div> `longitude` varchar(50) default NULL,</div><div> `lat_long_precision` varchar(50) default NULL,</div><div> `max_altitude` varchar(50) default NULL,</div><div> `min_altitude` varchar(50) default NULL,</div><div> `altitude_precision` varchar(50) default NULL,</div><div> `min_depth` varchar(50) default NULL,</div><div> `max_depth` varchar(50) default NULL,</div><div> `depth_precision` varchar(50) default NULL,</div><div> `continent_ocean` varchar(100) default NULL,</div><div> `country` varchar(100) default NULL,</div><div> `state_province` varchar(100) default NULL,</div><div> `county` varchar(100) default NULL,</div><div> `collector_name` varchar(255) default NULL,</div><div> `locality` text,</div><div> `year` varchar(50) default NULL,</div><div> `month` varchar(50) default NULL,</div><div> `day` varchar(50) default NULL,</div><div> `basis_of_record` varchar(100) default NULL,</div><div> `identifier_name` varchar(255) default NULL,</div><div> `identification_date` datetime default NULL,</div><div> `unit_qualifier` varchar(255) default NULL,</div><div> `created` timestamp NULL default NULL,</div><div> `modified` timestamp NULL default NULL,</div><div> `deleted` timestamp NULL default NULL,</div><div> PRIMARY KEY (`id`),</div><div> KEY `created` (`created`,`modified`),</div><div> KEY `data_resource_id` (`data_resource_id`,`catalogue_number`),</div><div> KEY `resource_access_point_id` (`resource_access_point_id`,`id`)</div><div>) ENGINE=MyISAM AUTO_INCREMENT=329086455 DEFAULT CHARSET=utf8</div><div><br></div><div><br></div><div><div>On Apr 9, 2011, at 1:47 PM, Herbario SANT wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>Good news</div> <div> </div> <div>Perhaps it is possible that you publish more detailed info of this test as an example. Providers handle they local data in many different ways (excel, access, whatever ...), but MySQL is pretty commonly available in shared institutional servers. </div> <div>So having a good working sample of a well designed database schema (properly indexed) for providing massive DarwinCore data would be very useful as an example to follow (I mean an sql file with CREATE sentences for tables and indexes).<br> </div> <div>Just an idea<br><br>-- <br></div> <div>David García San León</div> <div>Herbario SANT</div> <div>Facultade de Farmacia - Laboratorio de Botánica</div> <div>Universidade de Santiago de Compostela</div> <div>15782 - Galicia (Spain)</div> <div><a href="http://www.usc.es/herbario" target="_blank">http://www.usc.es/herbario</a></div> <div>Tel. +34 881815022</div> <div>Fax +34 981594912</div><br> _______________________________________________<br>IPT mailing list<br><a href="mailto:IPT@lists.gbif.org">IPT@lists.gbif.org</a><br>http://lists.gbif.org/mailman/listinfo/ipt<br></blockquote></div><br></body></html>