The select of XX, XX from ` table `
WHERE
(user_id='a7ae8176763b9643cc92edc4dc318 or cur_user_id=' a7ae81767e63b063cc92dc4dc3718 ')
AND ticket_time BETWEEN '02:29:18' 2018-08-15 AND '2018-08-16 02:29:18'
Innodb engine, user_id, cur_user_id, index, ticket_time date type, set up a general index
To view from the execution plan can't go index

CodePudding user response:

` field type ticket_time ` datetime DEFAULT NULL,

CodePudding user response:

You try a query time index is not the field to see him go?

CodePudding user response:

Ticket_time null values are stored, online see elsewhere for the interpretation of the null values

The index should not be stored null values
A. single-column index cannot store null values, a composite index can't store the values of all is null,

B. query, using is null condition, cannot use to the index, can only a full table scan,

Why is indexed column should not be stored Null values?

A. index is ordered, a NULL value into the index, unable to determine where it should be, (will be indexed column values, which inevitably involves many comparison operation, NULL values are uncertain value cannot be

Compared to determine the null in the index tree leaf node location,)

B. if you need to put a NULL value in the index, the method has two: first, the NULL values to a certain value, the WHERE is retrieved, with the specific value lookup, secondly, to create a composite index, such as

The create index ind_a on table (col1, 1); Through a non-empty specified in the composite index constant values, and make the form index of the combination of columns, unlikely the null values,

CodePudding user response:

If affect efficiency suggest you separate, time respectively with userid set up two joint index, with the union and union all you can be judged according to the specific business, there may be repeated if the data is the union to heavy, if there are no duplicate data using union all better, or a separate query, code-behind multithreaded processing,

CodePudding user response:

Hash values are not suitable for the index and make the efficiency is not high also