Leaderboard

MySQL/InnoDB is highly performant which is one of the reasons (along with simplicity) it continues to be used. All RDBMS's depend on indexing, and the fundamental index type they all provide and implement is the Btree index. FWIW, I have extensive experience with Sybase/Sql Server, Oracle and MySQL. MySQL is unique in that it has alternative engines, as well as forks (MariaDB, Percona). PostgreSQL is often preferred by people coming from Oracle, as it is very similar down to database objects, datatypes and the stored procedure/trigger language (PL/SQL) that are foundations of Oracle use through the years. Sybase/SQL Server were also popularized through use of the stored proc/trigger language T-SQL. Comparatively, MySQL didn't even have Stored Procedures/Triggers until version 5.x beta'd in 2005. The bigger question in the open source world is not MySQL vs the commercial DB's but rather MySQL vs PostgreSQL. Even more recently many RDBMS alternatives have come into the mainstream under the NoSQL or Document DB monikers, most notably MongoDB and CouchDB. This has to do with concerns about scalability, which you don't have reasonable expections will be an issue for you that can't be solved with a bigger server.

Years ago I wrote a web page (using VB with SQLServer) which showed all bus stops within 200m of your location (this could be changed by a slider) with their scheduled departure times/destinations in the next hour. This was fine out in the 'burbs where there might only be a handful of stops inside the search area. In the city centre it was a different picture with hundreds of stops and thousands of journeys. If the search radius exceed 50m it was timing out after 30secs. This was at a time when I had started using PHP but my bosses were against it (cos it wasn't Micro$oft). I migrated the required database tables so that I had a MySQL version and rewrote the VB code in PHP. Absolutely no problem in the city centre until I wound up the search radius to 2 Km. That was the last time I used VB.

If your structure is normalized correctly you should be fine. Make sure that your tables are using the InnoDB engine. Any queries that can be satisfied by using the primary key index return all data in the record by reading the index itself. This is because InnoDB has "clustered indexes" which is a fancy term meaning that the data is actually stored in the primary key index. All primary and foreign keys are inherently indexed. So the main concern in regards to other required indexes would be queries that have where clauses that aren't keys in the table definition. This is the single most important detail. Your queries need to use a relevant index. An obvious example would be username & password columns in a user table. Essentially you just need to focus on the WHERE clauses of your queries and make sure that you have an index to handle the query. For example, if you always query for user by username & password, have an index on username, password, and not individual indexes on username and password. If you have a monolithic server (webserver, php, mysql running on one box) then you have to plan for contention. MySQL needs some tweaking to provide a sufficient InnoDB buffer pool allocation. If it's a small DB as you describe your entire dataset can be in the buffer pool cache the majority of the time.

A single query with a join is more efficient than separate queries.