Adding index on comprofiler-related tables for performance

Hi,

I would assume that the building of SQL queries to populate userlists is already optimized, correct? So I was thinking of adding indexes on different cbfields to improve the performance of userlists. Basically, we are encountering a spike on CPU (we are at 3 cores already) whenever users are trying to access the userlists. Is it safe to add specific indices on the CB fields? If yes, what are your suggestions?

You can safely add indexes to any of CBs database tables as needed. This is actually very recommended for popular sites to ensure your queries are optimized to your usage. We've implemented optimized generic indexes, but we can't get any more specific with them as custom fields come into the picture.

Hi krileon,

Thank you for the kind reply.

I'm looking to add a lot of FULLTEXT indexes on the CB fields that have free text on them. If I add them via let's say, PHPMyAdmin, is that all I need to get the index working?

Also, I noticed that a lot of the custom fields have "Spatial" index by default. Do you think this is ok? Or should I switch indexes depending on the data that would be contained on that column?

lylejover wrote: If I add them via let's say, PHPMyAdmin, is that all I need to get the index working?

Yes.

lylejover wrote: Also, I noticed that a lot of the custom fields have "Spatial" index by default. Do you think this is ok? Or should I switch indexes depending on the data that would be contained on that column?

It depends on what queries you use most, but yes it's ok. You can remove/change indexes as needed, but I recommend making a backup of your database before doing so just to be safe.

Thanks, krileon!

One last question: If we have a CB field that is a dropdown list (example: Location), can the data type for that column be changed to ENUM without losing data integrity/userlist output?

I don't recommend changing a columns type. You're welcome to try, but I've no idea if that'll work right or not. Be sure to create a backup of your database before doing it though.