Slow Queries? Check the Cardinality of Your MySQL Indexes

Slow Queries? Check the Cardinality of Your MySQL Indexes

More indexes doesn’t always mean better performance

This week I’ve been heavily involved with optimizing the performance of the social networking features of a client site. The site in question uses the User Relationships module, a module that allows site members to connect with each other and share content within their circle of friends. The User Relationships module installs two key tables for storing relationships:

The {user_relationship_types} table contains configuration data for each relationship type. Supporting multiple relationship types allows users to have different kinds of relationships, such as those that are one-way (like Twitter), or require approval (like Facebook) on the same site.
The {user_relationships} table contains the actual relationships between users on the site. This table can be quite large, as it contains one row for every user relationship on the site.
How do these tables and their indexes relate to performance? Cardinality.

Leave a Reply

Your email address will not be published.