mysql - Join vs subquery to count nested objects -

-

let's model contains 2 tables: persons , addresses. 1 person can have o, 1 or more addresses. i'm trying execute query lists persons , includes number of addresses have respectively. here 2 queries have achieve that:

select   persons.*,   count(addresses.id) number_of_addresses `persons`   left join addresses on persons.id = addresses.person_id group persons.id

and

select   persons.*,   (select count(*)    addresses    addresses.person_id = persons.id) number_of_addresses `persons`

and wondering if 1 better other in term of performance.

the way determine performance characteristics run queries , see better.

if have no indexes, first better. if have index on addresses(person_id), second better.

the reason little complicated. basic reason group by (in mysql) uses sort. and, sorts o(n * log(n)) in complexity. so, time sort grows faster data (not faster, bit fast). consequence bunch of aggregations each person faster 1 aggregation person on data.

that conceptual. in fact, mysql use index correlated subquery, faster overall group by, not make use of index.


Comments

Post a Comment

Popular posts from this blog

Python Pandas join aggregated tables -

-
i have data frame 2 columns: product_id , rating. i'm trying join 2 tables. 1 obtained from a = data.groupby('product_id').count() the other one b = data.groupby('product_id').mean() i'd have table has on columns the product_id count mean try: data.groupby('product_id').agg(['mean', 'count'])
Read more

java - Static nested class instance -

-
this question has answer here: java inner class , static nested class 23 answers i came across in java, know static nested class why create instance of 'static'. isn't whole idea of 'static' use without instance? understand concept of inner classes, why (and frankly how possible to) create 'instance' of 'static' member @ ? why this: 1- outerclass.staticnestedclass nestedobject = new outerclass.staticnestedclass(); 2- nestedobject.anestedclassmethod(); and not this: 1- outerclass outerinstance=new outerclass(); 2- outerinstance.staticnestedclass.anestedclassmethod(); use on inner classes, keyword static indicates can access inner class without instance of outer class. for example: public class outer { public static class inner { /* code here. */ } } with construction, can create instance of inn...
Read more

process - Python What is the difference between a Pool of worker processes and just running multiple Processes? -

-
i not sure when use pool of workers vs multiple processes. processes = [] m in range(1,5): p = process(target=some_function) p.start() processes.append(p) p in processes: p.join() vs if __name__ == '__main__': # start 4 worker processes pool(processes=4) pool: pool_outputs = pool.map(another_function, inputs) as says on pymotw : the pool class can used manage fixed number of workers simple cases work done can broken , distributed between workers independently. the return values jobs collected , returned list. the pool arguments include number of processes , function run when starting task process (invoked once per child). please have @ examples given there better understand application, functionalities , parameters. basically pool helper, easing management of processes (workers) in cases need consume common input data, process in parallel , produce joint output. the pool quite few thi...
Read more