Just looking at the tables that p2p installs (p2p and p2p_meta) they contain a number of indexes, each on single columns. Note that a query can [generally] only use one index on a table, so some compound indexes may give better results.
Both tables have indexes on the meta_key and p2p_type columns, which has a very low cardinality compared to the id fields (p2p_id, p2p_to and p2p_from). That low cardinality makes those indexes pretty useless, and if MySQL chooses to use the index on meta_key, it could slow things down a lot as a website scales.
If the id fields are only ever used in queries with the other indexed columns in the WHERE-clause, then it is likely to give you better results if they are merged into compound indexes.
I’ve not got any stats yet, or tried any of this merging (I’m still importing a massive site) but when I do, I’ll come back to this post. In the meantime, those are my thoughts on just a casual glance, and with too many years of RDBMS experience tucked away.
- The topic ‘[Plugin: Posts 2 Posts] Use of compound indexes’ is closed to new replies.