Slow sql query despite indexes

Question

i'm stuck with a performance of a simple sql query.

Query:

SELECT * FROM company
        WHERE 
        (IDCompanyMain in (ID1, ID2...) or IDCompany in (ID1, ID2...))
        and Network="XXX";

There are indexes on each field and ca 10M rows. Each query has 1 to 100 ids.

Each query needs >4 seconds! if i remove one of the conditions, results are ready in miliseconds.

EXPLAIN says, mysql uses network index and has ca 5M rows in question. If try to force certain index(USE or FORCE), no index is used.

What i am doing wrong?

EXPLAIN:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'company', 'ref', 'PRIMARY,UNetwork_ID,IIDCompanyMain,INetwork', 'INetwork', '98', 'const', '5270286', 'Using where'

can you please run the same query with EXPLAIN prefix and post the explain result? — Jan Myszkier
– Jan Myszkier, Commented Aug 1, 2018 at 12:44

Coloured Panda · Accepted Answer · 2019-08-28 06:20:15Z

2

Only one index is being used - for the Network column.

Due to the OR clause, indexes for IDCompanyMain and IDCompany cannot be used. You'd have to do two separate queries for each of these conditions and then join the results together:

(SELECT * FROM company WHERE IDCompanyMain in (ID1, ID2...) and Network="XXX")
UNION 
(SELECT * FROM company WHERE IDCompany in (ID1, ID2...) and Network="XXX")

For optimal querying performance, you'd need two compound indexes - (Network, IDCompanyMain) and (Network, IDCompany)

edited Aug 28, 2019 at 6:20

answered Aug 1, 2018 at 13:17

Coloured Panda

3,5173 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jan Myszkier · Accepted Answer · 2018-08-01 13:49:05Z

1

the EXPLAIN result says only the Network field index is used in the check (see key column), making the check being run against over 5 milion rows and that's why it's so slow.

You should consider creating an index that uses both fields in the index, the indexes you have only index 1 field per index. see this for example: CREATE INDEX index_1 ON t1 (c1, c2); so in your case:

CREATE INDEX maincompany_network ON company (IDCompanyMain, Network);
CREATE INDEX company_network ON company (IDCompany, Network);

then EXPLAIN again to see if it improved your rows column.

I advised creating two indexes one for network and maincompany and another: network and company, becasue that's how your OR is going to be evaluated.

edited Aug 1, 2018 at 13:49

answered Aug 1, 2018 at 13:15

Jan Myszkier

2,7541 gold badge18 silver badges24 bronze badges

Collectives™ on Stack Overflow

Slow sql query despite indexes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related