Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  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)
share|improve this question
4  
Please don't make people guess what indexes you have. If you've got a query that's not using indexes, providing those index definitions is rather capital. –  Mat Feb 18 '13 at 5:51
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.