Folks,
I have a 677M row table with index
desc pts_awarded_snap;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| member_id | int(11) | | MUL | 0 | |
| PTS_AWARDED | int(11) | | | 0 | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
select count(*) from pts_awarded_snap;
+-----------+
| count(*) |
+-----------+
| 776723372 |
+-----------+
1 row in set (0.00 sec)
Which you can see has an index on member_id.
The problem is when I try to create a temporary table from a
portion of this based upon the member_id, the optimizer decides
that a table scan is necessary on this 7GB table.
When I try:
create temporary table T_awards_snap
select member_id, pts_awarded
from pts_awarded_snap FORCE INDEX (member_id)
where member_id between 700001 and 800000;
the optimizer decides to do a full table scan to choose the
required rows.
Yet a counting query of the same space:
select count(*) from pts_awarded_snap
where member_id between 700001 and 800000;
+----------+
| count(*) |
+----------+
| 3957677 |
+----------+
1 row in set (5.84 sec)
works fine.
explain select member_id, pts_awarded
from pts_awarded_snap FORCE INDEX (member_id)
where member_id between 700001 and 800000;
+------------------+-------+---------------+-----------+---------+------+------
---+-------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+------------------+-------+---------------+-----------+---------+------+------
---+-------------+
| pts_awarded_snap | range | member_id | member_id | 4 | NULL |
3885524 | Using where |
+------------------+-------+---------------+-----------+---------+------+------
---+-------------+
1 row in set (0.00 sec)
seems to indicate the member_id index can be used. But the length of time to
do this query, and the amount blocks being read as indicated by vmstat, tell
me that a full table scan is being performed.
I've looked over the optimization elements of the MySQL manual, trying
set max_seeks_for_key=100;
even 'FORCE INDEX (member_id)' has been tried as shown in the query above
to no avail, a table scan is still done.
Bottom line question out of all this: Is there a way to convince the
optimizer that it is fine to use a range portion of the index to fulfill
this query?
Please let me know if there is something I've missed,
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
