simple query is not using indexes

Question

1. My query is not using indexes.

2. it was taking around 5 seconds.

3. output is 500 rows only. it is a simple query

Please find the explain plan and table structure here.

Explain Plan

mysql> explain SELECT COUNT(*) AS `records_found`
               FROM `builds` AS `build`
               WHERE (`user_id` = '32762'
               AND `country_id` = 'gb'
               AND `share` = 0
               AND `mode` = 'bouquet'
               AND `build_data` != '');

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | build | ALL  | NULL          | NULL | NULL    | NULL | 35209 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Table structure

mysql> show create table builds\G
*************************** 1. row ***************************
       Table: builds
Create Table: CREATE TABLE `builds` (
  `build_id` varchar(8) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `derivation` int(11) NOT NULL,
  `share` tinyint(4) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `build_data` text NOT NULL,
  `mode` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`build_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Answer 1

According to your table structure, you only have a PRIMARY KEY on build_id.

Thus, there is no index that can be utilized to solve this query. I suspect you are very new to indexes; having an index on some column doesn't mean all queries will run faster. You need an index that matches you query.

For example, in your case, an index on user_id or an index on country_id or an index on share or an index on mode would work; Indexes on combinations of the above, like INDEX (user_id, country_id) would do well, too.

Which should you choose? One that would significantly limit the number of rows to "search by hand" (so to speak), and one that would do well for your other common queries.

My recommendation to you is to study how indexes work in relational databases.