Why do these MySQL queries take such a long time to process when they seem equivalent?

Question

The following are three MySQL queries. The first returns everything that I am looking for in one query, and in the second - the same aggregation results, but in two queries.

I am wondering why a single request takes 2 to 100 seconds longer when it seems that the process time should be equivalent between all three requests. Is there a way to optimize a single query to work as fast as separate queries? Adding more ORs inside the WHERE clause for a single query does not increase the process time, but I have cases where I need to do many more ORs and, ultimately, one query will be as fast as executing ten individual queries.

It seems that a single request gets cached after it is launched and may take minutes to run for the first time, while single requests always complete within the same timeframe.

Can multiple columns matter here?

It should be noted that the table does not have an identifier field as a primary index. Does this cause this unwanted behavior?

It is hard to run the tests here, as the table contains one hundred million rows, and adding columns and indexes takes close to the day.

SINGLE QUERY (4.2s)

SELECT name_id FROM staging_company_search WHERE (name_word_0 = 'the' AND name_word_1 = 'glazier') OR (name_word_0 = 'bridgewaters' AND name_word_1 = ''); 

EQUIVALENT AGGREGATE QUALIFICATIONS (0.8 s each )

 SELECT name_id FROM staging_company_search WHERE name_word_0 = 'the' AND name_word_1 = 'glazier'; SELECT name_id FROM staging_company_search WHERE name_word_0 = 'bridgewaters' AND name_word_1 = ''; 

EXPLAIN ON THIS QUERIES

 id select_type table type possible_keys key key_len ref rows extra 1 SIMPLE staging_company_search range name_word_0,name_word_1 name_word_0 102 NULL 2197605 Using index condition; Using where 1 SIMPLE staging_company_search ref name_word_0,name_word_1 name_word_1 102 const 128 Using index condition; Using where 1 SIMPLE staging_company_search ref name_word_0,name_word_1 name_word_0 102 const 33 Using index condition; Using where 

DATABASE DIAGRAM

 CREATE TABLE `staging_company_search` ( `name_id` int(11) unsigned NOT NULL DEFAULT '0', `name_word_0` varchar(100) NOT NULL, `name_word_1` varchar(100) NOT NULL, KEY `name_id` (`name_id`), KEY `name_word_0` (`name_word_0`), KEY `name_word_1` (`name_word_1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

Answer 1

This is because mysql uses only one index for a simple query.

If there is a choice between multiple indexes, MySQL usually uses an index that finds the least number of rows (the most selective index).

However, mysql can and will use two indexes from version 5.0 through Index Merge Optimization . Unfortunately, this does not always happen, and even when this happens, the results are not so great .

The first conclusion of the explanation shows that index merge optimization is not used in your query with the OR clause. It uses only the index name_word_0

name_word_0 is great for WHERE name_word_0 = 'the' AND name_word_1 = 'glazier'; but, as the output for the third explanation shows, it is not at all suitable for WHERE name_word_0 = 'bridgewaters' AND name_word_1 = '';

Therefore, the combined query is very slow. You can overcome this by creating a composite index that spans name_word_0 and name_word_1. I noticed that your key length is very long. You can create a partial index and possibly speed things up even further.

 CREATE INDEX word01 ON staging_company_search (name_word_0(20), name_word_1(20)) 

Answer 2

I believe this is due to the way MySQL handles indexes, where it should check for several conditions ("OR"). This can be seen from the “request for explanation”, where the first request must check more lines before returning the result.

I believe combining two small results should provide better performance. Can you try the following?

 SELECT name_id FROM staging_company_search WHERE (name_word_0 = 'the' AND name_word_1 = 'glazier') UNION ALL SELECT name_id FROM staging_company_search WHERE (name_word_0 = 'bridgewaters' AND name_word_1 = ''); 

Answer 3

Your use of the OR clause in the first query will result in a loss of use of your index. MySQL is a little dumb. It performs a full table scan: looks at each row. You are much better off using AND-only UNION queries together.

To make your I-requests even faster, create a composite index at (name_word_0, name_word_1, name_id) . Your queries can be completely satisfied with random access to this index and should start subseconds with two megarow tables.