具有大型数据集的MySQL关系数据库？

Question

I am trying to access data from two tables that share a common 'SpeciesName' field.

Table 'coords' is a very large dataset with over 5500 entries, and the table itself has around 26 fields. The Unique ID of this table is the Matrix Number, which runs from 0-5500, and each row contains slightly unique data. There are multiple rows with the same 'SpeciesName', however - there are only 446 unique 'SpeciesNames' within the table.

Table 'common' stores common name and image data for each 'SpeciesNames' with 3 fields. Basically what I am trying to do it set up a MySQL relation between the two tables using 'SpeciesNames' as the foreign key and access the data from both in one query.

Where to start? Google searches have yielded results dealing with tables containing one or two fields. However when I query, I want to select ALL fields from BOTH tables in PHP. Any advice regarding this would be wonderful.

Edit: I know the data isn't large at the moment, but it will be! I should have been clearer about that. This is 'coords':

`Authors` varchar(500) DEFAULT NULL,
  `Journal` varchar(500) DEFAULT NULL,
  `YearPublication` varchar(500) DEFAULT NULL,
  `DOIISBN` varchar(500) DEFAULT NULL,
  `AdditionalSource` varchar(500) DEFAULT NULL,
  `Ecoregion` varchar(500) DEFAULT NULL,
  `GrowthType` varchar(500) DEFAULT NULL,
  `GrowthFormRaunkiaer` varchar(500) DEFAULT NULL,
  `NumberPopulations` varchar(500) DEFAULT NULL,
  `AnnualPeriodicity` varchar(500) DEFAULT NULL,
  `CriteriaSize` varchar(500) DEFAULT NULL,
  `CriteriaOntogeny` varchar(500) DEFAULT NULL,
  `CriteriaAge` varchar(500) DEFAULT NULL,
  `Kingdom` varchar(500) DEFAULT NULL,
  `Phylum` varchar(500) DEFAULT NULL,
  `AngioGymno` varchar(500) DEFAULT NULL,
  `DicotMonoc` varchar(500) DEFAULT NULL,
  `Class` varchar(500) DEFAULT NULL,
  `_Order` varchar(500) DEFAULT NULL,
  `Family` varchar(500) DEFAULT NULL,
  `Genus` varchar(500) DEFAULT NULL,
  `SpeciesName` varchar(500) DEFAULT 'NA',
  `EnteredBy` varchar(500) DEFAULT NULL,
  `EnteredDate` varchar(500) DEFAULT NULL,
  `Source` varchar(500) DEFAULT NULL,
  `SpeciesAuthor` varchar(500) DEFAULT NULL,
  `StudiedSex` varchar(500) DEFAULT NULL,
  `MatrixComposite` varchar(500) DEFAULT NULL,
  `MatrixTreatment` varchar(500) DEFAULT NULL,
  `MatrixCaptivity` varchar(500) DEFAULT NULL,
  `MatrixStartYear` varchar(500) DEFAULT NULL,
  `MatrixStartSeason` varchar(500) DEFAULT NULL,
  `MatrixStartMonth` varchar(500) DEFAULT NULL,
  `MatrixEndYear` varchar(500) DEFAULT NULL,
  `MatrixEndSeason` varchar(500) DEFAULT NULL,
  `MatrixEndMonth` varchar(500) DEFAULT NULL,
  `Population` varchar(500) DEFAULT NULL,
  `LatDeg` varchar(500) DEFAULT NULL,
  `LatMin` varchar(500) DEFAULT NULL,
  `LatSec` varchar(500) DEFAULT NULL,
  `LonDeg` varchar(500) DEFAULT NULL,
  `LonMin` varchar(500) DEFAULT NULL,
  `LonSec` varchar(500) DEFAULT NULL,
  `LatitudeDec` varchar(500) DEFAULT NULL,
  `LongitudeDec` varchar(500) DEFAULT NULL,
  `Altitude` varchar(500) DEFAULT NULL,
  `Country` varchar(500) DEFAULT NULL,
  `Continent` varchar(500) DEFAULT NULL,
  `MatrixSplit` varchar(500) DEFAULT NULL,
  `Observation` varchar(500) DEFAULT NULL,
  `MatrixClassOrganized` varchar(500) DEFAULT NULL,
  `Matrixnumber` varchar(500) NOT NULL DEFAULT '',
  `MatrixClassNumber` varchar(500) DEFAULT NULL,
  `Dimension` varchar(500) DEFAULT NULL,
  `plantType` varchar(500) DEFAULT NULL,
  `matrix` varchar(15000) DEFAULT NULL,
  `_ClassNames` varchar(16000) DEFAULT NULL,
  `StatusStudy` varchar(500) DEFAULT NULL,
  `StatusStudyRef` varchar(500) DEFAULT NULL,
  `StatusElsewhere` varchar(500) DEFAULT NULL,
  `StatusElsewhereRef` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`Matrixnumber`),

And 'common':

  `ID` varchar(100) NOT NULL,
  `CommonName` varchar(100) NOT NULL,
  `ImageUrl` varchar(200) NOT NULL,
  `UploadImgUrl` varchar(100) NOT NULL,
  UNIQUE KEY `SpeciesAccepted` (`ID`),
  KEY `ID` (`ID`)

I'm basically plotting each record on a map using the coordinates and displaying all of the information from 'coords'- this has already been achieved successfully. The table 'common' contains new information that has been dynamically created for each 'SpeciesName' (coords.SpeciesName and common.ID), so I would like to display the CommonName and utilise the 'ImageUrl' and 'UploadedImgUrl' alongside the data I previously had. I'm still a bit of a newbie to MySQL, so I apologise if my terminology isn't perfect!

Answer 1

What you are looking for is called database normalization. The basic idea is to avoid having redundant data in a table and instead only keep a single value (SpeciesNames in your case) to which you reference.

So instead of having coords.SpeciesName, which contains the same string as common.CommonName you would store a reference as coords.SpeciesName_id which stores the common.ID. Both fields should be of type INT for perfomance resons, since they can be compared much quicker than strings.

To fetch data from both tables in a single query, you can do something like this:

SELECT * FROM coords JOIN common ON coords.SpeciesName_id = common.ID

A quick search on google yields this intro to normalization, for example.

Another search for PHP MySQL tutorial will give you hints on how to execute the query using PHP.