Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that looks like:

CREATE TABLE `connections` (
`connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`service_id` smallint(5) unsigned DEFAULT NULL,
`parent_id` bigint(20) unsigned DEFAULT NULL,
`child_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`connection_id`),
UNIQUE KEY `primary_child` (`parent_id`,`child_id`),
UNIQUE KEY `child_primary` (`child_id`,`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I need to do lookups on child_id and get information about parent_id and vice versa.

Ie:

SELECT COUNT(`parent_id`) FROM `connections` WHERE `child_id` = 'x'

or

SELECT COUNT(`child_id`) FORM `connections` WHERE `parent_id` = 'x'

And some more advanced queries like:

SELECT DISTINCT `parent_id`
FROM `connections` `c1`
INNER JOIN `connections` `c2`
ON `c1`.`parent_id` = `c2`.`parent_id`
WHERE `c1`.`child_id` = 'x'
AND `c2`.`child_id` = 'y'

I'm looking to get a better idea of the most efficient index structure to get quick responses from all the above query examples.

It should be noted that the child/parent pairs will be unique.

EDIT: This table currently holds 40M rows, likely to be much larger in the near future.

Thanks,

share|improve this question
    
The 2 composite indexes you already have are sufficient for the queries you mention. And I think the DISTINCT is not needed in the 2nd query. –  ypercube Mar 10 at 11:49

3 Answers 3

You already have indices on both colums with the unique constraint. No need for new ones.

I hope you have a good reason avoiding a foreign key. I hope you pay attention to keep your data consistent...

btw: parent_id in your join query is ambiguous.

share|improve this answer
  1. since primary_child (parent_id,child_id) is defined to be unique, then KEY child_primary (child_id,parent_id) IS unique, so you only have to define it as KEY, not UNIQUE KEY. [This is more helpful while inserting though]
  2. Having lots of NULL values is not recommended. It is not good for performance. If possible, add NOT NULL instead. However, this is highly dependent on your requirements. If there are lots of (parent_id, child_id) = (NULL, NULL), then create a new table with these two fields only, and link it to this table.
share|improve this answer

Unless you're using the composite indices for something else, it would be more efficient to us simple indices for child_id and parent_id respectively.

(More efficient as in needing less storage and creating less unnecessary overhead for inserts. Select speed should remain unchanged, since the composite indices are effectively used as simple index in your usage cases.)

share|improve this answer
    
If you downvote, could you please explain why? I'm pretty sure the count() functions above don't use the composite index above, neither the where (child) combined with the join on (parent). If that's incorrect I'd rather know than be left in the dark. –  Sascha Rambeaud Mar 11 at 15:37
    
The two queries will do use the composite indexes, just try them with small tables in a test database of yours. If parent_id was not nullable however, then the SELECT COUNT(parent_id) FROM connections WHERE child_id = 'x' could be replaced with SELECT COUNT(*) FROM connections WHERE child_id = 'x' and a single-column index on (child_id) would be enough. –  ypercube Jun 13 at 18:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.