Simple Query takes 50 seconds with Indexing

[Joms Calma]

I have a simple select query using only a single table with 160,000 records that takes too much time to load depending on the columns in the SELECT clause. It seems that the SELECT columns should be indexed as well for it to have a decent processing time, which means in the EXPLAIN statement there should be "Using Where; Using Index" in the Extra column for it to be fast, otherwise it takes 50 seconds to query. As I understand from 3 days of researching this only the columns in the AND group should be indexed.

Is this normal or there's something I need to fix? Can you suggest a way to optimize my query/table? I'm completely stumped.

mysql> SHOW CREATE TABLE ar_uitspraak;

ar_uitspraak | CREATE TABLE `ar_uitspraak` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ljn` varchar(40) NOT NULL DEFAULT '',
`instantie_naam` varchar(80) NOT NULL,
`datum_uitspraak` datetime NOT NULL,
`datum_gepubliceerd` datetime NOT NULL,
`status` varchar(40) NOT NULL,
`zaaknummers` varchar(255) NOT NULL,
`uitspraak_anoniem` text,
`conclusie_anoniem` text,
`rechtsgebied_rechtspraak` varchar(80) NOT NULL,
`procedure_soort` varchar(255) NOT NULL,
`indicatie` text,
`date_modified` datetime NOT NULL,
`kop` text,
PRIMARY KEY (`id`),
UNIQUE KEY `ljn` (`ljn`),
KEY `index_search` (`rechtsgebied_rechtspraak`,`instantie_naam`)
ENGINE=InnoDB AUTO_INCREMENT=166272 DEFAULT CHARSET=latin1 |

mysql> show table status like 'ar_uitspraak'\G;
*************************** 1. row ***************************
           Name: ar_uitspraak
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 400331
 Avg_row_length: 10456
    Data_length: 4185915392
Max_data_length: 0
   Index_length: 17891328
      Data_free: 4227858432
 Auto_increment: 166272
    Create_time: 2011-06-22 12:27:24
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.23 sec)

THIS IS VERY SLOW:

mysql> EXPLAIN select `id`,`status` from ar_uitspraak WHERE rechtsgebied_rechtspraak = 'Belasting';
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+-------------+
| id | select_type | table        | type | possible_keys | key          | key_len | ref   | rows  | Extra       |
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | ar_uitspraak | ref  | index_search  | index_search | 82      | const | 25922 | Using where |
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

THIS IS FAST:

mysql> EXPLAIN select `instantie_naam`, `rechtsgebied_rechtspraak` from ar_uitspraak WHERE rechtsgebied_rechtspraak = 'Belasting';
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+--------------------------+
| id | select_type | table        | type | possible_keys | key          | key_len | ref   | rows  | Extra                    |
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | ar_uitspraak | ref  | index_search  | index_search | 82      | const | 25922 | Using where; Using index |
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%buffer%'
    -> ;
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| bulk_insert_buffer_size      | 8388608  |
| innodb_buffer_pool_instances | 1        |
| innodb_buffer_pool_size      | 16777216 |
| innodb_change_buffering      | all      |
| innodb_log_buffer_size       | 8388608  |
| join_buffer_size             | 131072   |
| key_buffer_size              | 16777216 |
| myisam_sort_buffer_size      | 8388608  |
| net_buffer_length            | 8192     |
| preload_buffer_size          | 32768    |
| read_buffer_size             | 262144   |
| read_rnd_buffer_size         | 524288   |
| sort_buffer_size             | 524288   |
| sql_buffer_result            | OFF      |
+------------------------------+----------+
14 rows in set (0.03 sec)

I am running Windows 7 and have 4GB of RAM on my local machine.

Thank you.

Edit: After posting I just realized I posted it in the wrong forum, please just move it to the Performance forum instead if needed. Sorry for the trouble.



Edited 1 time(s). Last edit at 07/21/2011 08:28AM by Joms Calma.