Indexes - Create them for every variation of query or when fetching is slow?

SQL++
,
1

Hello,

I’ve been migrating an already in production application in the past week and I would like to know what would be the best approach.

Do you usually create an index for every single query variation that you create? Or just when data fetching starts becoming slow?

Another related question is regarding the creation of these indexes. I’ve thought eventually try to auto-create indexes, and if they already exists, an exception would be thrown (like it occurs in some examples in Couchbase Docs). However wouldn’t that be one more operation that should be avoided?

Thanks!

2

I think you should create the indexes in advance. I also think you should know and plan exactly how every query will execute and which indexes it will use. I don’t think you should wait until fetching becomes slow.

You should run EXPLAIN for every query in your application and make sure it is using a good index.

3

Thanks a lot for your advice @geraldss.

In my specific use case we haven’t really had time to plan for those changes, as we were aware that the change from MySQL to Couchbase would be now or never.

Regarding the following question, do you recommend something?

Another related question is regarding the creation of these indexes. I’ve thought eventually try to auto-create indexes, and if they already exists, an exception would be thrown (like it occurs in some examples in Couchbase Docs). However wouldn’t that be one more operation that should be avoided?

4

I do not recommend auto-creation of indexes, or even creation of indexes via application logic.

As a starting point, you can start with the indexes you have been using in MySQL, and just create those same indexes. Are they auto-generated in MySQL?

In general, the queries and indexes in MySQL should be the starting point for migrating to Couchbase.

We are very interested in these RDBMS to Couchbase migrations. You can share your queries here if you’d like. And if you run into functionality or performance that does not migrate directly from MySQL to Couchbase, we would love to hear about those specifics.

Gerald