performance - MySQL Speeding up left outer join / check for null queries -

-

the object of query rows table gender = f , username not exist in table b campid = xxxx. here query using success:

select `id`  pool    left join sent      on  pool.username = sent.username      , sent.campid = 'ya1lgfh9'  sent.username null    , pool.gender = 'f'

the problem query takes on 9 minutes complete, pool table contains on 10 million rows , sent table going grow larger that. have created indexes many of columns including username , gender. however, mysql refuses use of indexes query. tried using force index. here indexes pool , output of explain query:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | pool  |          0 | primary  |            1 | id          |         |     9326880 |     null | null   |      | btree      |         | | pool  |          1 | username |            1 | username    |         |     9326880 |     null | null   |      | btree      |         | | pool  |          1 | source   |            1 | source      |         |           6 |     null | null   |      | btree      |         | | pool  |          1 | gender   |            1 | gender      |         |           9 |     null | null   |      | btree      |         | | pool  |          1 | location |            1 | location    |         |       59030 |     null | null   |      | btree      |         | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 6 rows in set (0.00 sec)  mysql> explain select `id` pool force index (username) left join sent on pool.username = sent.username , sent.campid = 'ya1lgfh9' sent.username null , pool.gender = 'f'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    |                   | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ |  1 | simple      | pool  |  | null          | null | null    | null | 9326881 | using             | |  1 | simple      | sent  |  | null          | null | null    | null |     351 | using where; not exists | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ 2 rows in set (0.00 sec)

also, here indexes sent table:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sent  |          0 | primary  |            1 | primary_key |         |         351 |     null | null   |      | btree      |         | | sent  |          1 | username |            1 | username    |         |         351 |     null | null   |      | btree      |         | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)

you can see no indexes not being used , query takes extremely long. if has solution involves reworking query, please show me example of how using data structure won't have confusion of how implement , test. thank you.

first, original query correct in placement of everything... including camp. using left join pool sent, , pulling required equality such "camp" clause suggested converting inner join, requiring entry on both sides. leave had it.

you have index on user name on sent table, following.

build index on "sent" table on (campid, username) composite (ie: multiple key) index. way left join optimized both entries.

on "pool" table, try composite index on 3 fields of (gender, username, id ).

by doing this, can take advantage of not having go through actual pages of data encompass 10+ million records. since index has columns compare, doesn't have find actual record , @ columns, can use of index directly.

also, grins, added keyword "straight_join" tells mysql query show , don't try think me. many times, i've found improve query performance... on few have been given feedback has not helped.

select straight_join       p.id           pool p          left join sent s             on s.campid = 'ya1lgfh9'              , p.username = s.username                p.gender = 'f'       , s.username null

all said, still going returning how many records out of 10+ million... if pool has 10+ million, , single camp has 5,000. still returning entire set.


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more