Optimization problem?

[Shirik]

My site's been getting shut down about once a day for a few minutes as the host puts a limit on our CPU usage. I'm assuming that it's a problem with our database optimization (even if it seems like it's been put together very well, in my opinion). But... status vars suggest otherwise:

 

Handler_read_rnd 84 M 

The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

 

Handler_read_rnd_next 33 G

The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have

 

Select_full_join  273 k 

The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

 

Select_range_check  1,273 

The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)

 

 

 

So I'm browsing around on our site checking out the queries and I come across one. I run an EXPLAIN on it and here's what comes out (query's slightly modified, but no difference in execution):

mysql> EXPLAIN SELECT * FROM ibf_sessions WHERE running_time > 10000000;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ibf_sessions | ALL  | NULL          | NULL | NULL    | NULL |   41 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.56 sec)

 

I know enough to know that "ALL" is bad. So I figure I need an index on running_time... so I add it.

 

mysql> SHOW KEYS IN ibf_sessions;
+--------------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| ibf_sessions |          0 | PRIMARY      |            1 | id              | A         |          39 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | location1    |            1 | location_1_type | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | location1    |            2 | location_1_id   | A         |          19 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | location2    |            1 | location_2_type | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | location2    |            2 | location_2_id   | A         |          13 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | location3    |            1 | location_3_type | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | location3    |            2 | location_3_id   | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| ibf_sessions |          1 | running_time |            1 | running_time    | A         |          39 |     NULL | NULL   | YES  | BTREE      |         |
+--------------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.09 sec)

 

But there's no difference in execution. I get the slightly modified EXPLAIN which (as far as I can tell) shows no change:

mysql> EXPLAIN SELECT * FROM ibf_sessions WHERE running_time > 10000000;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ibf_sessions | ALL  | running_time  | NULL | NULL    | NULL |   39 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.08 sec)

 

And the inherent problem with this is...

 

mysql> EXPLAIN SELECT name FROM ibf_members
    -> INNER JOIN ibf_sessions
    -> WHERE ibf_members.id = ibf_sessions.member_id
    ->  AND ibf_sessions.running_time > 1000000;
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+------+-------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                                       | rows | Extra       |
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+------+-------------+
|  1 | SIMPLE      | ibf_sessions | ALL    | running_time  | NULL    | NULL    | NULL                                      |   39 | Using where |
|  1 | SIMPLE      | ibf_members  | eq_ref | PRIMARY       | PRIMARY | 3       | heroesof_ghqforums.ibf_sessions.member_id |    1 |             |
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+------+-------------+
2 rows in set (0.09 sec)

 

So I'd like to get this fixed. Does anyone see what I'm missing? Because I'm sure it's something really stupid. Note that this query listed in the very first EXPLAIN is used on every single page load, so it does need to be as good as I can get it, really.

 

Thanks for any help,

-- Shirik

[Shirik]

Ah, sorry forgot my version:

 

mysql> SELECT VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 5.0.27-standard-log |
+---------------------+
1 row in set (0.09 sec)

 

And the structure of ibf_sessions:

mysql> DESCRIBE ibf_sessions;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | varchar(32)  | NO   | PRI | 0       |       |
| member_name     | varchar(64)  | YES  |     | NULL    |       |
| member_id       | mediumint( | NO   |     | 0       |       |
| ip_address      | varchar(16)  | YES  |     | NULL    |       |
| browser         | varchar(200) | NO   |     |         |       |
| running_time    | int(10)      | YES  |     | NULL    |       |
| login_type      | char(3)      | YES  |     |         |       |
| location        | varchar(40)  | YES  |     | NULL    |       |
| member_group    | smallint(3)  | YES  |     | NULL    |       |
| in_error        | tinyint(1)   | NO   |     | 0       |       |
| location_1_type | varchar(10)  | NO   | MUL |         |       |
| location_1_id   | int(10)      | NO   |     | 0       |       |
| location_2_type | varchar(10)  | NO   | MUL |         |       |
| location_2_id   | int(10)      | NO   |     | 0       |       |
| location_3_type | varchar(10)  | NO   | MUL |         |       |
| location_3_id   | int(10)      | NO   |     | 0       |       |
+-----------------+--------------+------+-----+---------+-------+
16 rows in set (0.08 sec)

 

Again, thanks for any help.

[btherl]

It's quite difficult to know when to use an index for a "greater than" condition.  If a lot of values match the condition, then using an index will actually make it slower.  If you know there will only be a small number of matching rows, you might want to try FORCE INDEX.

 

On the other hand, if there actually are a small number of rows in that table at all times, then using an index may not help.

 

Have you profiled your program to see which queries are taking up the time?  It's good to be sure that that query is one of the problem queries before trying to optimize it.  A good approach is to use a wrapper for your database queries which times every single one, and logs the query along with the time taken to a log file.

[fenway]

Did you run ANALYZE TABLES?