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.