CODEWITHSUNDEEP
mysqldatabase
Adam
Adam

Reputation: 20952

MySQL INNODB Optimisation and Indexes

I'm designing a MySQL Database...

  1. I have a number of tables that use UserID as the primary Key. All these tables will have 1 row for each user and will therefore grow at the same rate. Seeing they will be access via the primary key (UserID) I assume there is no need to index to this key as each is unique.

  2. I will also have a number of tables that will have an incremental number as a primary key and the UserID will be second column. For these tables there will be multiple instances of the UserID in the second column. (each this UserID has many Friends and each is listed on a different row). I expect these tables to grow very long, however not very wide and therefore not large with respects to GB.

Question: If I add an index on the tables in part 2 above (the UserID/second column) and if my queries are using the UserID for Joins (all joins off UserID) does this mean Joins would access the larger tables (Part 2 above) via the indexed UserID and therefore the speed of access these larger tables would be similar to the smaller tables in Part 1?

Having all tables that will be part of Joins using the UserID as either a unique Primary Key (shorter tables) or an Indexed column (larger tables) - does this appear to be resonable design with respects to ensure good response times if tables were to get quite large - ever in to the 100Millions rows? (excluding other requirements like HW).

thoughts? thx

Upvotes: 0

Views: 100

Answers (2)

melihcelik
melihcelik

Reputation: 4599

If a column is (or a set of columns are) defined to be primary key of the table, there will be an index associated with it (them). See MySQL reference on Primary Keys.

If you're implementing queries that have JOINs, then as Marc B already noted, it's generally a good practice to add index on the column that you join on (userId in your case).

if my queries are using the UserID for Joins (all joins off UserID) does this mean Joins would access the larger tables (Part 2 above) via the indexed UserID

However, as you've asked, it's not certain that MySQL will use the index that you've created. Depending on the structure of your query and the possible data in the resulting set, MySQL Query Optimizer might decide to not use the index that you've added. The way to find if MySQL uses your index is to run EXPLAIN query. See its syntax and how to use it to optimize the queries. You can guide the query optimizer to use certain indexes which might save time on both query plan analysis and query execution performance but I suggest you to run several tests to make sure the index that you enforced is really better. You should also keep in mind that as the data in the tables grow, your query might be less performant, so you should regularly check the performance of your queries and arrange your indexes.

If the second table in your query will always be queried on userId field, then you might as well want to leverage Partitioning support that's given with MySQL 5.1 onwards. Partitioning shards records in your main table into several tables behind the scenes and can really boost the performance of your queries if you follow its rules.

Upvotes: 1

Marc B
Marc B

Reputation: 360872

A primary key field(s) is automatically part of a unique index, so there's no need to add yet another index on top of that.

It is a good rule of thumb to put an index onto any field which is used in a where, join, and/or order by clause.

Speed-wise, no one can say for sure if adding (or removing) indexes will improve performance. For simple/small DBs, indexes are invariably a major speedup. For large/complicated schemas, they can actually hurt performance in some scenarios. You'll have to benchmark your system to make sure. But in general, indexes = good.

Upvotes: 2

Related Questions