search

michaeldrennen / Geonames

A Laravel (php) package to interface with the geo-location services at geonames.org.
MIT License
89 stars 38 forks source link

Add an index #20

Closed Nuranto closed 5 years ago

Nuranto commented 5 years ago

alternate_name should have its index. Maybe with isPreferredName ? Usefull if we want to propose cities from a postcode. (Postcodes are stored in alternate_name of table geonames_alternate_names)

Nuranto commented 5 years ago

To do that, you'll need to use InnoDB instead of MyISAM. I know MyISAM is a bit faster for reading, but I think it should be ban. With myisam, there is no data integrity and no transactions - which is a problem itself - which means it is not clusterable...

michaeldrennen commented 5 years ago

I agree it should have an index. I've added one onto the alternate_name field. I am hard-pressed to imagine a use-case where we'd benefit from an index on another field. Let me know if you can think of one.

Regarding InnoDB vs MyISAM: Once you start using this package, your local copy of the database should only be written to once a day. That is as often as Geonames.org updates their records. It makes sense to bias toward read versus write. As for "clustering", there are plenty of solutions for spreading the load of a read-heavy database across multiple boxes that don't require a change to the architecture of the package. Right? Thanks, and feel free to respond to my questions, and I'll re-open the ticket if needed.

Nuranto commented 5 years ago

@michaeldrennen Ok, it is arguable. However, I had to switch to innodb because of key length limit. My DB is utf8mb4. You have option in InnoDB : innodb_large_prefix, but there's no solution with MyISAM but to reduce the key size (as far as I know..)