CODEWITHSUNDEEP
mysql
d.raev
d.raev

Reputation: 9546

MySQL indexing char(1) columns

I have a table with a complex query that I look for optimization, I read most of the documentation on MySQL indexing .. but in this case I`m not sure what to do:

Data structure: 

-- please, don't comment on the field types and names, it is outsourced project.

CREATE TABLE items(
  record_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  solid CHAR(1) NOT NULL, -- only 'Y','N' values
  optional CHAR(1) NULL, -- only 'Y','N', NULL values
  data TEXT
 );

Query:

SELECT * FROM items
WHERE record_id != 88
AND solid = 'Y'
AND optional !='N'  -- 'Y' OR NULL

Of course there are extra joins and related data, but this are the biggest filters.

In the scenario of:
- 200 000+ records,
- 10% (from all) with solid = 'Y',
- 10% (from all) with optional !='N',

What would be good index for this query ?
or more precisely:


As @Jack requested, current EXPLAIN result (out of 30 000 total rows with 20 results):

+-------------+-------+--------------+---------+---------+------+-------+-------------+
| select_type | type  | possible_key |   key   | key_len | ref  | rows  |    Extra    |
+-------------+-------+--------------+---------+---------+------+-------+-------------+
| PRIMARY     | range | PRIMARY      | PRIMARY |       4 | NULL | 16228 | Using where |
+-------------+-------+--------------+---------+---------+------+-------+-------------+

Upvotes: 0

Views: 1375

Answers (2)

Barmar
Barmar

Reputation: 781004

You should try to put indexes on the columns that will do the most discrimination. Usually indexing a binary column is not very helpful, if the database is about evenly split between the values. But if the value you often search for only appears 10% of the time, it can be a useful index.

If any of the columns are indexed, they will usually be checked before doing any other WHERE processing. The order that you put the conditions in the WHERE clause is not generally relevant. You can use EXPLAIN to find out which indexes a query uses.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is an interesting question. Overall, your query has an estimated selectivity of about 1%. So, if 100 records fit on a page, then you would assume that each page would still have to be read, even with the index. Because a record is so small (depending on data that is), this is quite likely. From that perspective, an index is not worth it.

An index would be worth it under the following circumstances. The first is when the index is a covering index, meaning that you can satisfy the query with all the columns in the index. For example:

select count(*)
FROM items
WHERE record_id != 88 AND solid = 'Y' AND optional !='N'  -- 'Y' OR NULL

Where the index is on solid, optional, record_id. The query doesn't need to go back to the original data pages.

Another case would be when the index is a primary (or clustered) index. The data is stored in that order, so fetching a limited number of results would reduce the read overhead of the query. The downside to this is that updates and inserts are more expensive, because data actually has to move.

My best guess in your case is that an index would not be useful, unless data is quite large (in the kilobyte range).

Upvotes: 3

Related Questions