Liczenie rekordów Opcje

[acztery]

witam,

mam tabele z 220 tys rekordów

i chce policzyc wszystkie ktore spelniaja jakis warunek ale to tra bardzo dlugo. ok 30 sek.

[SQL] pobierz, plaintext 
SELECT Count(id) 
FROM prg
 WHERE UserID='Ux1'
[SQL] pobierz, plaintext 

czy mysql ma jakies wbudowane funkcje np count_big tak jak w sql server 2000

Ten post edytował acztery 15.03.2006, 00:18:26

[DeyV]

Manual MySQL podpowiada

If you very often need to calculate results such as counts based on information from a lot of rows, it's probably much better to introduce a new table and update the counter in real time. An update of the following form is very fast:

[SQL] pobierz, plaintext 
UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
[SQL] pobierz, plaintext 

This is really important when you use MySQL storage engines such as MyISAM and ISAM that have only table-level locking (multiple readers / single writers). This will also give better performance with most databases, because the row locking manager in this case will have less to do.