2012/6/7 A.L.E.C
<alec@alec.pl>
Dziwne. Jaki masz czas gdy zamienisz update na select?
SELECT id FROM documents WHERE customerid IN
(SELECT a.customerid FROM cash a WHERE a.time <= unix_timestamp()
GROUP BY a.customerid HAVING SUM(a.value) >= 0)
AND type IN (1, 3, 5) AND cdate <= unix_timestamp() AND closed = 0
jak miałem rozliczone (przez poprzedni update) faktury to ten select wykonał się szybko.
przywróciłem bazę w której jest nie zamkniętych około 3000 faktur u prawie 900 klientów. to teraz ten select już długo kręci i nie przestaje;
mysql> show processlist;
+----+------+-----------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 44 | root | localhost | nicraM | Query | 675 | Sending data | SELECT id FROM documents WHERE customerid IN
(SELECT a.customerid FROM cash a WHERE a.time <= unix_ |
| 45 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
a zestaw 3 zapytań:
mysql> CREATE TEMPORARY TABLE tabela_tymczasowa AS SELECT customerid FROM cash WHERE time <= unix_timestamp() GROUP BY customerid HAVING SUM(value) >= 0;ALTER TABLE tabela_tymczasowa ADD UNIQUE (customerid);UPDATE documents d RIGHT JOIN tabela_tymczasowa t ON t.customerid=d.customerid SET closed = 1 WHERE type IN (1, 3, 5) AND cdate <= unix_timestamp() AND closed = 0;
Query OK, 876 rows affected (2.14 sec)
Records: 876 Duplicates: 0 Warnings: 0
Query OK, 876 rows affected (0.03 sec)
Records: 876 Duplicates: 0 Warnings: 0
Query OK, 2922 rows affected (0.30 sec)
Rows matched: 2922 Changed: 2922 Warnings: 0
mysql>
jak widać różnica kolosalna.
Jaka wersja MySQLa?
mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu
jest to system do testów postawiony na virtualboxie na lapku.