DB index documentation & horse name uniqueness?

0 votes
DB table & fields documentation is brilliant - Thank you!! Is there any documentation on the indexing of the fields and tables?

Before I buy I'd like to know how easy & efficient my queries are going to be. For example I know can write a query to pull out the details of the last 3 races for each of the runners in a race and see the race going & race distance, etc... but without decent indexing this could be 'relatively slow'. It looks like I would have to use the horse name(since no unique horse id) and sort by race date descending limit 1, 3, this gives me the race_ids of the prev 3 races and then lookup directly. Is the DB indexed by horse name, date, or anything? Obviously I could copy the tables and create my own indexed versions(indexed according to my own needs) but I thought I'd ask so I know how efficient my queries will be or how much work I need to do.

It doesn't look like there is a unique horse_id. Is horse name unique over the full history of the database, can I be sure that looking at all history for 'horse name' is exactly the same 'horse name' across all records in the DB? Can horses have the same name as a horse that is now retired/dead? Would I have to check the foaling date every time I lookup on horse name?

Thanks and look forward to working with the data! I've scraped races from 2006 through present, including getting the cards every day, but a site changes their html the data then becomes unreliable and it's just not worth the hassle anymore so I'm up for paying for it!!
asked Apr 11, 2018 in Smartform by BobBob Plater (190 points)

1 Answer

+1 vote
Hi - horse_id, race_id and various other id fields (jockey, trainer etc) are available so can all be indexed efficiently.
answered Apr 13, 2018 by colin Frankel (19,280 points)
So can I be sure that looking at all history for 'horse name' is exactly the same 'horse name' across all records in the DB?
Yes - though if you use horse_name as opposed to horse_id it will help to add country of breeding.
...