MySQL indexes - how to boost performace?

Question

I'm trying to improve performance of an existing MySQL database.

It's a database regarding restaurants, there are two relevant tables:

there's a table for all entities of the website, every entity has a unique id, an entity can be pretty much anything, it can be a restaurant, a user and many other things. there are several entity types and as for restaurants, their entity type is "object".

Let me also say that this structure of the database is pretty much existing so I don't want to make big changes, I'm not going to remove the table of all the entities for example. (the Database itself has no data, but the PHP engine is built so it'll be hard to make big changes to the structure).

there's also a table only for objects, there are several types of objects in that database but restaurants specifically are going to be searches for a lot since that's the subject of the website, restaurants have several fields: country, city, name, genre. there can't be two restaurants with the same name in the same city and country, (There CAN be for example two restaurant with the same name but in different cities of the same country or in two cities that have the same name but are in different countries)

so from this fact I guess I should make a unique three-column index for the country, city and name columns.

Also I want to say that the URL is also built in the form of www.domain.com/Country/City/Restuarant-Name, so the combination of country-city-name should be fetched fast and this type of query will happen a lot.

But also there'll be queries of a lot of other types like: searching for a name of a restaurant (using a LIKE query because the name searched for can be a part of the full name) in a certain city, or in a certain country. searching for all the restaurants of a certain genre in a certain country and city. and pretty much all the combination possible.

Probably the most used queries will be (a) searching for a restaurant name in a certain city and country (which will be the same as the query used when a URL is typed but will use LIKE), (b) searching for restaurants of a certain type in a certain city and country. and lastly (c) searching for a restaurant name globally (in the whole database, without specifying the city and the country)

this table (the objects table) currently has PRIMARY KEY that is the ID of the objects, the ID is also used a lot, would the best practice be the following?:

1. make a three-column UNIQUE index out of country,city,name
2. make another (not-unique) index out of the names (so a query of type c which I've wrriten above will be executed fast)
3. maybe make some kind of a sub-table that contains only the restaurants out of the objects table so this sub-table will be queried. (this is less important since if I'll decide to make a large change I'll probably seperate the restaurants from the rest of the object to begin with)

I'd really appreciate any help cause I've been trying to decide this for a long time.

p.s in the objects table some of the objects won't have any genre or any country or city, so they will stay NULL, I know that NULL values are allowed in a UNIQUE KEY but will it have an impact on performace?

Thanx alot for anyone who was willing to read this long question :)

Answer 1

You can think and plan as long as you want, but you won't know for certain what's best until you try, benchmark, and compare your options. That said, it certainly sounds like you're definitely on the right track.

composite key
Your "country-city-name" composite key appears to be in the most useful order, since it's ordered from broadest to narrowest selection criteria. I'm sure you did this intentionally, as a composite key's values can only be used from left to right. Because name does not come first in that index, you'd need a separate key for just name, as you noted.

index values of NULL
According to imysql.cn, "allowing NULL values in the index really doesn't impact performance." That's simply stated as an aside without data or reference, so I don't know how/if they'd proven that.

splitting the table
If there's a lot of other data mixed in with the restaurant records, sure, it could slow things a bit. If you shard the table into identically-structured "restaurant" and "other" tables, you could still easily query their combined data if necessary with a simple UNION. Unless you have an idea of the data/slowdown to expect, I'd prefer to avoid sharding the table unless necessary, at least for the sake of simplicity/uniformity.

Are there any foreseeable queries that current indexing wouldn't account for, such as a city without the country? If so, be sure to index appropriately to cover all foreseeable cases. You didn't mention it, but I assume you'll also have an index on genre.

---

Ultimately, you need to generate lots of test data and try it out. (Determine how much data you could eventually expect, and generate at least triple that much test data to put the system through its paces.) From what you've described, the design sounds pretty good, but testing may reveal unexpected issues, places where you'd benefit from different indexing, etc. With any issue found, you'd have a specific goal to accomplish rather than simply pondering all what-if scenarios.