CODEWITHSUNDEEP
mysql
Stefan Weiss
Stefan Weiss

Reputation: 461

Slow sql query despite indexes

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'

Upvotes: 1

Views: 69

Answers (2)

Coloured Panda
Coloured Panda

Reputation: 3467

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)

Upvotes: 2

Jan Myszkier
Jan Myszkier

Reputation: 2744

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.

Upvotes: 1

Related Questions