Hello James,
Thank you for your response. Here are the information that gives more insight to my scenario. The problem is that my SELECT takes longer (1.03 min on 101841 rows of data). I am looking in ways to improve it, based on my setup. Any suggestions is truly appreciated. Thanks. Please note, I have one INDEX on column URI, which i am using in my WHERE clause.
SAMPLE SELECT
mysql> select field_3, field_4, field_9, field_10, field_11, field_12 from book_tbl where URI_CM='ABCDEFGHI12345678910ABVDEFG';
+-------------+-------------+------------+------------+----------+----------+
| field_3 | field_4 | field_9 | field_10 | field_11 | field_12 |
+-------------+-------------+------------+------------+----------+----------+
| 0.5252525 | 0.5252525 | 0 | 47 | 99 | 99 |
| 0.47474748 | 0.47 | 52 | 0 | 47 | 47 |
| 0.53 | 0.53 | 0 | 47 | 100 | 100 |
| 0.47 | 0.47 | 53 | 0 | 47 | 47 |
| 0.53 | 0.53 | 0 | 47 | 100 | 100 |
| 0.47 | 0.74603176 | 53 | 0 | 47 | 47 |
| 1 | 0.25396827 | 0 | 47 | 63 | 63 |
| ... | ... | ... | ... | ... | ... |
+-------------+-------------+------------+------------+----------+----------+
101841 rows in set (1.03 sec)
TABLE:
mysql> describe book_tbl;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| URI | varchar(80) | YES | MUL | NULL | |
| field_1 | int(10) unsigned | YES | | NULL | |
| field_2 | double | YES | | NULL | |
| field_3 | double | YES | | NULL | |
| field_4 | int(10) unsigned | YES | | NULL | |
| field_5 | int(10) unsigned | YES | | NULL | |
| field_6 | double | YES | | NULL | |
| field_7 | varchar(40) | YES | | NULL | |
| field_8 | varchar(40) | YES | | NULL | |
| URI_CM1 | varchar(50) | NO | PRI | NULL | |
| URI_CM2 | varchar(50) | NO | PRI | NULL | |
| field_9 | int(10) unsigned | YES | | NULL | |
| field_10 | int(10) unsigned | YES | | NULL | |
| field_11 | int(10) unsigned | YES | | NULL | |
| field_12 | int(10) unsigned | YES | | NULL | |
| text1 | varchar(1000) | YES | | NULL | |
| text2 | varchar(1000) | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
EXPLAIN
mysql> explain select field_3, field_4, field_9, field_10, field_11, field_12 from book_tbl where URI_CM='ABCDEFGHI12345678910ABVDEFG';
+----+-------------+-----------+------+---------------+---------------+---------+-------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+-------+-----------------------+
| 1 | SIMPLE | book_tbl | ref | book_tbl_idx | book_tbl_idx | 243 | const | 60558 | Using index condition |
+----+-------------+-----------+------+---------------+---------------+---------+-------+-------+-----------------------+
1 row in set (0.00 sec)
SHOW INDEX
mysql> show index from book_tbl;
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book_tbl | 0 | PRIMARY | 1 | URI_CM1 | A | NULL | NULL | NULL | | BTREE | | |
| book_tbl | 0 | PRIMARY | 2 | URI_CM2 | A | 192627054 | NULL | NULL | | BTREE | | |
| book_tbl | 1 | book_tbl_idx | 1 | URI | A | 8375089 | NULL | NULL | YES | BTREE | | |
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS WHERE Name = 'book_tbl';
+-----------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| book_tbl | MyISAM | 10 | Dynamic | 192627054 | 909 | 175138952400 | 281474976710655 | 10765568000 | 0 | NULL | 2016-05-24 01:58:55 | 2016-06-04 19:00:59 | 2016-05-24 02:07:13 | utf8_general_ci | NULL | | |
+-----------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)