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
Unsolved 
