Databases
StackOverflow2013
Postsdbo
POhow to rewrite query replacing of (or),(and) conditions
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to rewrite query replacing of (or),(and) conditions
    text
    copied!<ol> <li>query taking too much time to execute like around 130 mins.</li> <li>table having indexes but query is not using indexes.</li> <li>table size is around 30 millions of records.</li> <li>please help me to rewrite the query by changing (or),(and) conditions .</li> <li>The query is not using in normal execution so we tried with force index but no luck.</li> </ol> <p>Query:</p> <pre><code>SELECT customer_history.customer_id, customer_history.row_mod, customer_history.row_create, customer_history.event_id, customer_history.event_type, customer_history.new_value, customer_history.old_value FROM customer_history FORCE INDEX (customer_history_n2) WHERE customer_id &gt;= 1 AND customer_id &lt; 5000000 AND (customer_history.row_mod &gt;= '2012-10-01') OR ( customer_history.row_create &gt;= '2012-10-01' AND customer_history.row_create &lt; '2012-10-13' ); Table structure CREATE TABLE `customer_history` ( `customer_id` int(11) NOT NULL, `row_mod` datetime DEFAULT NULL, `row_create` datetime DEFAULT NULL, `event_id` int(11) DEFAULT NULL, `event_type` varchar(45) DEFAULT NULL, `new_value` varchar(255) DEFAULT NULL, `old_value` varchar(255) DEFAULT NULL, KEY `customer_id1` (`customer_id`), KEY `new_value_n1` (`new_value`), KEY `customer_history_n1` (`row_create`), KEY `customer_history_n2` (`row_mod`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 </code></pre> <p>Explain Plan:</p> <pre><code>+----+-------------+------------------+------+---------------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | customer_history | ALL | customer_history_n2 | NULL | NULL | NULL | 18490530 | Using where | +----+-------------+------------------+------+---------------------+------+---------+------+----------+-------------+ 1 row in set (0 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload