mysql - Alternative to database table joins while filtering data -
we have portal member signup. members participate in topics, discussions, rewards etc. guess similar stackoverflow or other forums. each of our client can customise portal. 1 of customisable option member profile fields.
any number of profile fields can added in portal, done clients. more 10 not uncommon.
problem related filtering members based on these profile fields. if have many profile fields in portal , filter has lots of criteria database query doesn't work in realtime. e.g. if below database query takes 10.57 secs complete filter 10 profile fields:
select count(*) portal_member pm , profile_field_choice_value cr0 , profile_field_choice_value cr1 , profile_field_choice_value cr2 , profile_field_choice_value cr3 , profile_field_choice_value cr4 , profile_field_choice_value cr5 , profile_field_choice_value cr6 , profile_field_choice_value cr7 , profile_field_choice_value cr8 , profile_field_choice_value cr9 , profile_field_choice_value cr10 pm.portal_id = 56 , pm.status = 2 , ( cr0.client_id = 2130 , cr0.portal_id = 56 , pm.id = cr0.portal_member_id , (cr0.custom_field_id = 2334 , cr0.custom_field_choice_id in (289739 ) ) , (cr1.client_id = 2130 , cr1.portal_id = 56 , pm.id = cr1.portal_member_id , ( cr1.custom_field_id = 2335 , cr1.custom_field_choice_id in (290057 ) ) ) , (cr2.client_id = 2130 , cr2.portal_id = 56 , pm.id = cr2.portal_member_id , ( cr2.custom_field_id = 2340 , cr2.custom_field_choice_id in (288285 ) ) ) , (cr3.client_id = 2130 , cr3.portal_id = 56 , pm.id = cr3.portal_member_id , ( cr3.custom_field_id = 5006 , cr3.custom_field_choice_id in (301530 ) ) ) , (cr4.client_id = 2130 , cr4.portal_id = 56 , pm.id = cr4.portal_member_id , ( cr4.custom_field_id = 5477 , cr4.custom_field_choice_id in (302751 ) ) ) , (cr5.client_id = 2130 , cr5.portal_id = 56 , pm.id = cr5.portal_member_id , ( cr5.custom_field_id = 2342 , cr5.custom_field_choice_id in (288294 ) ) ) , (cr6.client_id = 2130 , cr6.portal_id = 56 , pm.id = cr6.portal_member_id , ( cr6.custom_field_id = 2622 , cr6.custom_field_choice_id in (289738 ) ) ) , (cr7.client_id = 2130 , cr7.portal_id = 56 , pm.id = cr7.portal_member_id , ( cr7.custom_field_id = 5478 , cr7.custom_field_choice_id in (302756 ) ) ) , (cr8.client_id = 2130 , cr8.portal_id = 56 , pm.id = cr8.portal_member_id , ( cr8.custom_field_id = 5479 , cr8.custom_field_choice_id in (302764 ) ) ) , (cr9.client_id = 2130 , cr9.portal_id = 56 , pm.id = cr9.portal_member_id , ( cr9.custom_field_id = 5480 , cr9.custom_field_choice_id in (302771 ) ) ) , (cr10.client_id = 2130 , cr10.portal_id = 56 , pm.id = cr10.portal_member_id , ( cr10.custom_field_id = 5481 , cr10.custom_field_choice_id in (302792 ) ) ) ); below table structures:
create table `portal_member` ( `id` int(11) not null auto_increment, `portal_id` int(11) not null default '0', `status` int(11) default null, `email_address` varchar(128) default null, `source` int(11) default null primary key (`id`), key `portal_id` (`portal_id`,`status`), key `id_portal_idx` (`id`,`portal_id`) ) engine=innodb auto_increment=22222 default charset=utf8 | create table `profile_field_choice_value` ( `id` int(11) not null auto_increment, `client_id` int(11) default null, `portal_id` int(11) default null, `portal_member_id` int(11) default null, `custom_field_id` int(11) default null, `custom_field_choice_id` int(11) default null, `t` datetime default null, `last_history_id` int(11) default null, primary key (`id`), key `client_id` (`client_id`,`portal_id`,`portal_member_id`), key `custom_field_idx` (`custom_field_id`), key `portal_idx` (`portal_id`) ) engine=myisam auto_increment=111111 default charset=utf8 | my question is:
- is there alternative database joins? e.g. maybe can process data cron job or , filtering on that?
- am missing obvious , doing joins incorrectly. , can solved in database only.
any appreciated. thank advance! if don't see solution can guide me in right direction great. don't mind reading book or learn tech if have to.
mysql explain result:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------+-----------------------------------------------------------+------+-------------+ | 1 | simple | cr9 | ref | client_id,custom_field_idx,portal_idx | custom_field_idx | 5 | const | 1 | using | | 1 | simple | pm | eq_ref | primary,portal_id,id_portal_idx | primary | 4 | sa.cr9.portal_member_id | 1 | using | | 1 | simple | cr10 | ref | client_id,custom_field_idx,portal_idx | custom_field_idx | 5 | const | 2 | using | | 1 | simple | cr3 | ref | client_id,custom_field_idx,portal_idx | custom_field_idx | 5 | const | 3 | using | | 1 | simple | cr5 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr4 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr7 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr8 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr6 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr1 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr2 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr0 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | update1:
tried 1 more query using inner join takes 10.50 secs:
select count(*) portal_member pm inner join profile_field_choice_value cr0 on cr0.client_id = 2130 , cr0.portal_id = 56 , pm.id = cr0.portal_member_id , ( cr0.custom_field_id = 2334 , cr0.custom_field_choice_id in (289739 ) ) inner join profile_field_choice_value cr1 on (cr1.client_id = 2130 , cr1.portal_id = 56 , pm.id = cr1.portal_member_id , ( cr1.custom_field_id = 2335 , cr1.custom_field_choice_id in (290057 ) ) ) inner join profile_field_choice_value cr2 on (cr2.client_id = 2130 , cr2.portal_id = 56 , pm.id = cr2.portal_member_id , ( cr2.custom_field_id = 2340 , cr2.custom_field_choice_id in (288285 ) ) ) inner join profile_field_choice_value cr3 on (cr3.client_id = 2130 , cr3.portal_id = 56 , pm.id = cr3.portal_member_id , ( cr3.custom_field_id = 5006 , cr3.custom_field_choice_id in (301530 ) ) ) inner join profile_field_choice_value cr4 on (cr4.client_id = 2130 , cr4.portal_id = 56 , pm.id = cr4.portal_member_id , ( cr4.custom_field_id = 5477 , cr4.custom_field_choice_id in (302751 ) ) ) inner join profile_field_choice_value cr5 on (cr5.client_id = 2130 , cr5.portal_id = 56 , pm.id = cr5.portal_member_id , ( cr5.custom_field_id = 2342 , cr5.custom_field_choice_id in (288294 ) ) ) inner join profile_field_choice_value cr6 on (cr6.client_id = 2130 , cr6.portal_id = 56 , pm.id = cr6.portal_member_id , ( cr6.custom_field_id = 2622 , cr6.custom_field_choice_id in (289738 ) ) ) inner join profile_field_choice_value cr7 on (cr7.client_id = 2130 , cr7.portal_id = 56 , pm.id = cr7.portal_member_id , ( cr7.custom_field_id = 5478 , cr7.custom_field_choice_id in (302756 ) ) ) inner join profile_field_choice_value cr8 on (cr8.client_id = 2130 , cr8.portal_id = 56 , pm.id = cr8.portal_member_id , ( cr8.custom_field_id = 5479 , cr8.custom_field_choice_id in (302764 ) ) ) inner join profile_field_choice_value cr9 on (cr9.client_id = 2130 , cr9.portal_id = 56 , pm.id = cr9.portal_member_id , ( cr9.custom_field_id = 5480 , cr9.custom_field_choice_id in (302771 ) ) ) inner join profile_field_choice_value cr10 on (cr10.client_id = 2130 , cr10.portal_id = 56 , pm.id = cr10.portal_member_id , ( cr10.custom_field_id = 5481 , cr10.custom_field_choice_id in (302792 ) ) ) pm.portal_id = 56 , pm.status = 2; explain result above query:
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------+-----------------------------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------+-----------------------------------------------------------+------+-------------+ | 1 | simple | cr9 | ref | client_id,custom_field_idx,portal_idx | custom_field_idx | 5 | const | 1 | using | | 1 | simple | pm | eq_ref | primary,portal_id,id_portal_idx | primary | 4 | sa.cr9.portal_member_id | 1 | using | | 1 | simple | cr10 | ref | client_id,custom_field_idx,portal_idx | custom_field_idx | 5 | const | 2 | using | | 1 | simple | cr3 | ref | client_id,custom_field_idx,portal_idx | custom_field_idx | 5 | const | 3 | using | | 1 | simple | cr5 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr4 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr7 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr8 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr6 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr1 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr2 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | | 1 | simple | cr0 | ref | client_id,custom_field_idx,portal_idx | client_id | 15 | const,const,sa.cr9.portal_member_id | 5 | using | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------+-----------------------------------------------------------+------+-------------+ update 2:
i able find 1 of query client used. query never completes. profile_field_choice_value table client has 280k rows. below sql query our team tried using inner:
select count(distinct pm.id) portal_member_id pm inner join profile_field_choice_value cr0 on pm.id = cr0.portal_member_id_id , cr0.custom_field_id = 3697 , cr0.custom_field_choice_id in ( 19588 ) , cr0.portal_id = 683 , cr0.client_id = 223 inner join profile_field_choice_value cr1 on pm.id = cr1.portal_member_id_id , cr1.custom_field_id = 3706 , cr1.custom_field_choice_id in ( 19623 ) , cr1.portal_id = 683 , cr1.client_id = 223 inner join profile_field_choice_value cr2 on pm.id = cr2.portal_member_id_id , cr2.custom_field_id = 3709 , cr2.custom_field_choice_id in ( 19646 ) , cr2.portal_id = 683 , cr2.client_id = 223 inner join profile_field_choice_value cr3 on pm.id = cr3.portal_member_id_id , cr3.custom_field_id = 3711 , cr3.custom_field_choice_id in ( 19653 ) , cr3.portal_id = 683 , cr3.client_id = 223 inner join profile_field_choice_value cr4 on pm.id = cr4.portal_member_id_id , cr4.custom_field_id = 3712 , cr4.custom_field_choice_id in ( 19659 ) , cr4.portal_id = 683 , cr4.client_id = 223 inner join profile_field_choice_value cr5 on pm.id = cr5.portal_member_id_id , cr5.custom_field_id = 3713 , cr5.custom_field_choice_id in ( 19663 ) , cr5.portal_id = 683 , cr5.client_id = 223 inner join profile_field_choice_value cr6 on pm.id = cr6.portal_member_id_id , cr6.custom_field_id = 3714 , cr6.custom_field_choice_id in ( 19667 ) , cr6.portal_id = 683 , cr6.client_id = 223 inner join profile_field_choice_value cr7 on pm.id = cr7.portal_member_id_id , cr7.custom_field_id = 3715 , cr7.custom_field_choice_id in ( 19672 ) , cr7.portal_id = 683 , cr7.client_id = 223 inner join profile_field_choice_value cr8 on pm.id = cr8.portal_member_id_id , cr8.custom_field_id = 3716 , cr8.custom_field_choice_id in ( 19678 ) , cr8.portal_id = 683 , cr8.client_id = 223 inner join profile_field_choice_value cr9 on pm.id = cr9.portal_member_id_id , cr9.custom_field_id = 3720 , cr9.custom_field_choice_id in ( 19714 ) , cr9.portal_id = 683 , cr9.client_id = 223 inner join profile_field_choice_value cr10 on pm.id = cr10.portal_member_id_id , cr10.custom_field_id = 3722 , cr10.custom_field_choice_id in ( 19744 ) , cr10.portal_id = 683 , cr10.client_id = 223 inner join profile_field_choice_value cr11 on pm.id = cr11.portal_member_id_id , cr11.custom_field_id = 3725 , cr11.custom_field_choice_id in ( 19807 ) , cr11.portal_id = 683 , cr11.client_id = 223 inner join profile_field_choice_value cr12 on pm.id = cr12.portal_member_id_id , cr12.custom_field_id = 3726 , cr12.custom_field_choice_id in ( 19816 ) , cr12.portal_id = 683 , cr12.client_id = 223 inner join profile_field_choice_value cr13 on pm.id = cr13.portal_member_id_id , cr13.custom_field_id = 3638 , cr13.custom_field_choice_id in ( 19169 ) , cr13.portal_id = 683 , cr13.client_id = 223 inner join profile_field_choice_value cr14 on pm.id = cr14.portal_member_id_id , cr14.custom_field_id = 3641 , cr14.custom_field_choice_id in ( 19190 ) , cr14.portal_id = 683 , cr14.client_id = 223 inner join profile_field_choice_value cr15 on pm.id = cr15.portal_member_id_id , cr15.custom_field_id = 3685 , cr15.custom_field_choice_id in ( 19326 ) , cr15.portal_id = 683 , cr15.client_id = 223 inner join profile_field_choice_value cr16 on pm.id = cr16.portal_member_id_id , cr16.custom_field_id = 3686 , cr16.custom_field_choice_id in ( 19329 ) , cr16.portal_id = 683 , cr16.client_id = 223 inner join profile_field_choice_value cr17 on pm.id = cr17.portal_member_id_id , cr17.custom_field_id = 3687 , cr17.custom_field_choice_id in ( 19522 ) , cr17.portal_id = 683 , cr17.client_id = 223 inner join profile_field_choice_value cr18 on pm.id = cr18.portal_member_id_id , cr18.custom_field_id = 3688 , cr18.custom_field_choice_id in ( 19528 ) , cr18.portal_id = 683 , cr1.client_id = 223 inner join profile_field_choice_value cr19 on pm.id = cr19.portal_member_id_id , cr19.custom_field_id = 3690 , cr19.custom_field_choice_id in ( 19533, 19534 ) , cr19.portal_id = 683 , cr19.client_id = 223 inner join profile_field_choice_value cr20 on pm.id = cr20.portal_member_id_id , cr20.custom_field_id = 3691 , cr20.custom_field_choice_id in ( 19541 ) , cr20.portal_id = 683 , cr20.client_id = 223 inner join profile_field_choice_value cr21 on pm.id = cr21.portal_member_id_id , cr21.custom_field_id = 3692 , cr21.custom_field_choice_id in ( 19552 ) , cr21.portal_id = 683 , cr21.client_id = 223 inner join profile_field_choice_value cr22 on pm.id = cr22.portal_member_id_id , cr22.custom_field_id = 3793 , cr22.custom_field_choice_id in ( 19561 ) , cr22.portal_id = 683 , cr22.client_id = 223 inner join profile_field_choice_value cr23 on pm.id = cr23.portal_member_id_id , cr23.custom_field_id = 3794 , cr23.custom_field_choice_id in ( 19572 ) , cr23.portal_id = 683 , cr23.client_id = 223 inner join profile_field_choice_value cr24 on pm.id = cr24.portal_member_id_id , cr24.custom_field_id = 3795 , cr24.custom_field_choice_id in ( 19581 ) , cr24.portal_id = 683 , cr24.client_id = 223 inner join profile_field_choice_value cr25 on pm.id = cr25.portal_member_id_id , cr25.custom_field_id = 3702 , cr25.custom_field_choice_id in ( 19614 ) , cr25.portal_id = 683 , cr25.client_id = 223 inner join profile_field_choice_value cr26 on pm.id = cr26.portal_member_id_id , cr26.custom_field_id = 3704 , cr26.custom_field_choice_id in ( 19616 ) , cr26.portal_id = 683 , cr26.client_id = 223 inner join profile_field_choice_value cr27 on pm.id = cr27.portal_member_id_id , cr27.custom_field_id = 3705 , cr27.custom_field_choice_id in ( 19618 ) , cr27.portal_id = 683 , cr27.client_id = 223 inner join profile_field_choice_value cr28 on pm.id = cr28.portal_member_id_id , cr28.custom_field_id = 3707 , cr28.custom_field_choice_id in ( 19630 ) , cr28.portal_id = 683 , cr28.client_id = 223 inner join profile_field_choice_value cr29 on pm.id = cr29.portal_member_id_id , cr29.custom_field_id = 3708 , cr29.custom_field_choice_id in ( 19643 ) , cr29.portal_id = 683 , cr29.client_id = 223 pm.portal_id = 683 , pm.client_id = 223 , pm.status = 2;
the explain shows indexes not cover query. means every filter field add, server has more work; means adding indexes cover query should speed index dramatically.
database engines @ joins. "relational" part in rdbms that; long joins can use indexes, can handle huge numbers of joins on massive data sets.
try adding following index:
create index filter on profile_field_choice_value (portal_member_id_id, custom_field_id, custom_field_choice_id, portal_id, client_id );
Comments
Post a Comment