Replication : How to load balance queries ?

MySQL & MariaDB Other MySQL® Questions
1

ng One table VAL_FAKTA_VH containing 5.000.000 rows and one table VAL_DIM_AVTAL containing 34 rows. The query takes 15 s using Index and 7 s not using index, how is this possible!!! It is not a question about IO, no IOWAIT but alot of CPU 99% (on machines with single CPU and 49.9 om machines with 2 CPU, MySQL does not seems to utilize both CPU:s)

HW
2*2Ghz CPU
8G RAM

I have used huge-conf with the following add:
join_buffer_size 131072
key_buffer_size 3221225472
tmp_table_size 67108864
read-only
and some more

DB:
VAL_FAKTA_VH.MYD ~ 600M
VAL_FAKTA_VH.MYI ~ 400M
VAL_DIM_AVTAL.MYI ~ 2M
VAL_DIM_AVTAL.MYD ~ 1M

mysql> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V force index(Index__avtalid)

2

, VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = ‘Huvud’ group by avtal.avtal;
±—±------------±------±-----±---------------±------- --------±--------±--------------------±-------±--------- —+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+

3

t_join other questions suffer.
Should not the optimizer se this and do the appropriate thing in this case? Can the optimizer learn from earlier queries?

Background to problem:
We will be creating this kind of questions from our Cognos-platform and are kean not to build these kind of exceptions in to our model. The Cognos environment are to be exposed to 1000 end users and these questions are generated by Cognos. In our model now with SQL Server this is not a problem.

Is there a way