izvini za kasni odgovor ali .. obaveze .. igracke ..
prvi hint, posto koristis myisam, sve varchar kolone prebaci u char. time ce tabele zauzeti neznatno vise mesta ali ce ti row biti "fixed size" sto je mnoooooooooogo brze nego dynamic kada je myisam u pitanju, posebno za select. Sa "show table status\G" dobijas statuse svih tabela .. bitan ti je "row format".
drugi hint, analyze table
to je nevezano direktno za problem, direktno vezano za pitanje, napunio sam bazu dummy podacima tako da imam slican explain kao ti, jedino imam malo vise slogova da bi se bolje videla razlika izmedju indexiranog i neindexiranog upita
Code:
mysql> explain extended
-> select * , inputprice * (1 +
-> (SELECT u.margin / 100 FROM `user` as u WHERE u.id =
-> (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`)))
-> from temprecord as t WHERE MONTH(t.starttime) = "6" AND YEAR(t.starttime) = "2009";
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 786473 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | u | eq_ref | PRIMARY,id | PRIMARY | 4 | func | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | p | ALL | NULL | NULL | NULL | NULL | 393216 | 100.00 | Using where |
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
kao sto vidis, kod mene ih ima malo "vise" od 6 u sub sub selectu ... i evo pustio sam da se izvrsi upit .. traje vec 15min i nije gotov :(
prvi deo
Code:
mysql> SELECT SQL_NO_CACHE userid FROM phonenumber p, temprecord t WHERE p.number = t.`from`;
+--------+
| userid |
+--------+
| 1 |
| 1 |
| 2 |
| 2 |
+--------+
4 rows in set (2.45 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE userid FROM phonenumber p, temprecord t WHERE p.number = t.`from`;
+----+-------------+-------+------+---------------+----------+---------+---------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+---------------+--------+-------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 393216 | |
| 1 | SIMPLE | t | ref | uniq_cdr | uniq_cdr | 22 | test.p.number | 2 | Using index |
+----+-------------+-------+------+---------------+----------+---------+---------------+--------+-------------+
2 rows in set (0.00 sec)
ne valja ... da malo budznemo:
Code:
mysql> alter table phonenumber add key i_number (number, userid);
Query OK, 393216 rows affected (1.41 sec)
Records: 393216 Duplicates: 0 Warnings: 0
mysql> explain SELECT SQL_NO_CACHE userid FROM phonenumber p, temprecord t WHERE p.number = t.`from`;
+----+-------------+-------+-------+---------------+----------+---------+---------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+---------------+--------+--------------------------+
| 1 | SIMPLE | p | index | i_number | i_number | 26 | NULL | 393216 | Using index |
| 1 | SIMPLE | t | ref | uniq_cdr | uniq_cdr | 22 | test.p.number | 2 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+---------------+--------+--------------------------+
2 rows in set (0.00 sec)
mnogo bolje, bar po tome sto kaze explain ...
da uporedimo sada rezultate upita sa i bez ovog malog kljuca:
Code:
mysql> select SQL_NO_CACHE * , inputprice * (1 + (SELECT u.margin / 100 FROM `user` as u WHERE u.id = (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`))) from temprecord as t WHERE MONTH(t.starttime) = "12" AND YEAR(t.starttime) = "2008";
+--------+------+----+---------------------+----------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id | from | to | starttime | duration | inputprice | outputprice | invoiceid | inputprice * (1 + (SELECT u.margin / 100 FROM `user` as u WHERE u.id = (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`))) |
+--------+------+----+---------------------+----------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 789813 | 9 | | 2008-12-01 00:00:00 | 559 | 8.89967 | 7.72802 | 1941 | NULL |
| 789814 | 10 | | 2008-12-01 00:00:00 | 652 | 6.78307 | 4.35161 | 1409 | NULL |
...
+--------+------+----+---------------------+----------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
100 rows in set (25.85 sec)
mysql> alter table phonenumber add key i_number (number, userid);Query OK, 393216 rows affected (1.38 sec)
Records: 393216 Duplicates: 0 Warnings: 0
mysql> select SQL_NO_CACHE * , inputprice * (1 + (SELECT u.margin / 100 FROM `user` as u WHERE u.id = (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`))) from temprecord as t WHERE MONTH(t.starttime) = "12" AND YEAR(t.starttime) = "2008";
+--------+------+----+---------------------+----------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id | from | to | starttime | duration | inputprice | outputprice | invoiceid | inputprice * (1 + (SELECT u.margin / 100 FROM `user` as u WHERE u.id = (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`))) |
+--------+------+----+---------------------+----------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 789813 | 9 | | 2008-12-01 00:00:00 | 559 | 8.89967 | 7.72802 | 1941 | NULL |
| 789814 | 10 | | 2008-12-01 00:00:00 | 652 | 6.78307 | 4.35161 | 1409 | NULL |
...
| 789912 | 108 | | 2008-12-01 00:00:00 | 851 | 4.92597 | 9.10496 | 747 | NULL |
+--------+------+----+---------------------+----------+------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
100 rows in set (0.21 sec)
sa 25 sekundi na pola sekunde :D .. ja bi rekao da je to uspeh ...
e sad .. da se vrnemo na onaj tvoj update .. da vidimo da li je cela stvar upotrebljiva ...
Code:
mysql> update temprecord as t SET outputprice = inputprice * (1 +
-> (SELECT u.margin / 100 FROM `user` as u WHERE u.id =
-> (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`)))
-> WHERE MONTH(t.starttime) = "12" AND YEAR(t.starttime) = "2008";
Query OK, 100 rows affected (1.34 sec)
Rows matched: 100 Changed: 100 Warnings: 0
mysql> alter table phonenumber drop key i_number;
Query OK, 393216 rows affected (0.70 sec)
Records: 393216 Duplicates: 0 Warnings: 0
mysql> update temprecord as t SET outputprice = inputprice * (1 + (SELECT u.margin / 100 FROM `user` as u WHERE u.id = (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`))) WHERE MONTH(t.starttime) = "12" AND YEAR(t.starttime) = "2008";
Query OK, 0 rows affected (26.79 sec)
Rows matched: 100 Changed: 0 Warnings: 0
eto, nismo prepisali query (i to bi moglo) vec dodali jedan mali kljuc i ubrzali upit 2000% :)