CODEWITHSUNDEEP
mysqltheory
Alan A
Alan A

Reputation: 2551

MySQL design theory for 20,000+ records

I am storing sporting fixture in a mysql database. At presnt I have 20,000 records approx, of which approx 3000 are current fixtures (this year). The current fixtures I will need to access, read and update on a regular basis, the older fixtures I only need to gain access to periodically.

So my plan is to put current fictures into one table and then keep the current fixture in another:

fixtures_histroic fixture_current

Is this good practice, my theory is I do not wan to have to trawl through 20,000 records when only 3,000 are possible results. Then when a new season or year starts I can copy the current fixtures into the historical table and start a new current.

This all sounds good in practice, my next question is how to search both table for data. I know how to do a simple join, but consider this query, how would I query both matches_current and matches_histroic. The query is already initself a join so would it be a join of joins. Looks like it might get quite messy?

SELECT `m`.*, `h`.`name` AS  "homeTeam", `a`.`name` AS  "awayTeam", `o`.*
FROM `matches_current` m
JOIN `teams` `h` ON `m`.`home_team_id`=`h`.`team_id` 
JOIN `teams` `a` ON `m`.`away_team_id`=`a`.`team_id`
JOIN `outcomes` `o` ON `m`.`score_id`=`o`.`outcome_id`
WHERE `home_team_id`=1 AND `away_team_id`=2 
AND `m`.`score_id`>0 
ORDER BY `date` DESC

So is what i'm doing correct, or should I just put all 20,000 records in a single table?

Thank you in advance,

Alan.

Upvotes: 0

Views: 622

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Concerning your "first" question, keeping "historical" and "current" data separate:

I don't think this would have any beneficial impact considering you will have such a small number of rows. On the opposite, this will make things much more complicated...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Lousy idea. 20,000 records is not very many. You can probably do what you want with a decent indexing scheme on the data. And, splitting the same data across multiple tables poses problems for maintenance, security, backups, and data integrity.

The only reasons you would do this on such a small number of records would be if the record size were inordinately large. So, even if the records were 10,000 bytes, then the total space taken by the table would be only 200 Mbytes -- which is still small by database standards.

If you had more rows and wanted to handle this situation, I would recommend partitioning the table rather than creating another table.

Upvotes: 1

Related Questions