Re: Fastest way to find if any rows match

[Jan Hansen]

Bill, you've been helping me out with questions related to this in the Performance area, so some of this will be familiar. This is the output from EXPLAIN SELECT.

mysql> EXPLAIN SELECT COUNT(current) FROM sentences WHERE current LIKE 'with%';
+----+-------------+-------------+-------+---------------+------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------------+---------+------+--------+-------------+
| 1 | SIMPLE | sentences | range | current | current | 32 | NULL | 93028 | Using where |
+----+-------------+-------------+-------+---------------+------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

As you can see, it does indeed use the index, and the 'key_buffer_size' is set to somewhat larger than the size of the 'sentences.MYI' file, which should mean it is able to hold the entire index. Preloading the index using LOAD INDEX INTO CACHE, following your suggestion, doesn't seem to make any useful difference, unfortunately - the query is still taking several minutes to complete. I'm going to try ANALYZE TABLE and OPTIMIZE TABLE next to see if that makes a difference.

In this thread I was just trying to determine if there was another way of doing this withou hitting the COUNT problem.