Above 2 lakh makes server crash : mysql 5.1, fedora 8

[hbmarar]

Hi,

For 2 lakh records, this works well but when the total number of queries is more than 5 lakh it does not . Actually i have 64 lakh records to work with and this gets forever.

UPDATE tmp_yk_contributor set score_last_update = '$date' , ykscore =  (IFNULL((select avg(score)*0.8 from yk_entity_score where yk_entity_score.user_id = tmp_yk_contributor.user_id) , 4) + IFNULL((select avg(participation_score)*2 from yk_participation_score where yk_participation_score.user_id = tmp_yk_contributor.user_id), 1)) where tmp_yk_contributor.user_id is not null ;

>desc yk_contributor
+--------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------------------+----------------+
| contributor_id | int(10) | NO | PRI | NULL | auto_increment |
| type | char(1) | NO | MUL | E | |
| identifier | varchar(255) | NO | | | |
| verified | char(1) | NO | | N | |
| ykscore | float | YES | | NULL | |
| user_id | int(11) | YES | MUL | NULL | |
| primary_identifier | char(1) | NO | | N | |
| date_added | date | NO | | 0000-00-00 | |
| score_last_update | datetime | NO | | 0000-00-00 00:00:00 | |
| opt_out | char(1) | YES | | N | |
| image | blob | YES | | NULL | |
+--------------------+--------------+------+-----+---------------------+----------------+
and
tmp_yk_contributor above in query is the temporary table made of yk_contributor.

Explain of individual queries gives me the following:

mysql> EXPLAIN select avg(participation_score)*2 from yk_participation_score,yk_contributor where yk_participation_score.user_id = yk_contributor.user_id

-> ;
+----+-------------+------------------------+------+---------------+---------------+---------+-----------------------------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------+---------------+---------+-----------------------------------------+---------+--------------------------+
| 1 | SIMPLE | yk_participation_score | ALL | NULL | NULL | NULL | NULL | 1041624 | |
| 1 | SIMPLE | yk_contributor | ref | index_user_id | index_user_id | 5 | scorecsa.yk_participation_score.user_id | 56924 | Using where; Using index |
+----+-------------+------------------------+------+---------------+---------------+---------+-----------------------------------------+---------+--------------------------+
2 rows in set (0.00 sec)

mysql> explain select avg(score)*0.8 from yk_entity_score,yk_contributor where yk_entity_score.user_id = yk_contributor.user_id
    -> ;

+----+-------------+-----------------+-------+-------------------------------+-------------------------------+---------+---------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+-------------------------------+-------------------------------+---------+---------------------------------+---------+-------------+
| 1 | SIMPLE | yk_contributor | index | index_user_id | index_user_id | 5 | NULL | 1024638 | Using index |
| 1 | SIMPLE | yk_entity_score | ref | index_yk_entity_score_user_id | index_yk_entity_score_user_id | 5 | scorecsa.yk_contributor.user_id | 1 | Using where |
+----+-------------+-----------------+-------+-------------------------------+-------------------------------+---------+---------------------------------+---------+-------------+
2 rows in set (23.52 sec)

I am stuck here and do not know how to go ahead with some optimisation. Request some help. thanks in advance

Saff

[varmadba]

1.
      mysql> EXPLAIN SELECT avg(participation_score)*2 FROM yk_participation_score,yk_contributor WHERE yk_participation_score.user_id = yk_contributor.user_id

mysql> EXPLAIN select avg(participation_score)*2 from yk_participation_score,yk_contributor where yk_participation_score.user_id = yk_contributor.user_id
-> ;
+----+-------------+------------------------+------+---------------+---------------+---------+-----------------------------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------+---------------+---------+-----------------------------------------+---------+--------------------------+
| 1 | SIMPLE | yk_participation_score | ALL | NULL | NULL | NULL | NULL | 1041624 | |
| 1 | SIMPLE | yk_contributor | ref | index_user_id | index_user_id | 5 | scorecsa.yk_participation_score.user_id | 56924 | Using where; Using index |
+----+-------------+------------------------+------+---------------+---------------+---------+-----------------------------------------+---------+--------------------------+
2 rows in set (0.00 sec)

it clearly says on yk_participation_score database is doing a full table scan

Does yk_participation_score have any index on user_id?
Does yk_contributor have index on user_id?

if not add index on these 2 tables on user_id column and review explain o/p