Tema
Boje
Pismo
Registracija

Novo

Forum kao aplikacija na telefonu

Dodajte forum na početni ekran i koristite ga kao aplikaciju, sa menijem na dnu ekrana.

Kako se dodaje

Update tabele koristenjem subquerija

Odgovori Nova tema
Štampanje RSS teme Kopiraj vezu do teme Podeli na Facebook-u Podeli na X-u (Twitter) Idi na poslednju poruku
  • 8 odgovora
  • 2.377 pregleda

Najnovija ↓

Update tabele koristenjem subquerija 03.06.2009. u 13:38 · pre 211 meseci #1
Struktura tabela:
Code:

mysql> desc temprecord;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| from        | varchar(20)      | NO   | MUL | NULL    |                | 
| to          | varchar(20)      | NO   |     | NULL    |                | 
| starttime   | datetime         | NO   |     | NULL    |                | 
| duration    | int(5) unsigned  | NO   |     | NULL    |                | 
| inputprice  | float unsigned   | NO   |     | 0       |                | 
| outputprice | float unsigned   | YES  |     | 0       |                | 
| invoiceid   | int(10) unsigned | YES  |     | NULL    |                | 
+-------------+------------------+------+-----+---------+----------------+

mysql> desc phonenumber; 
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| userid | int(10) unsigned | YES  |     | NULL    |                | 
| number | varchar(20)      | YES  |     | NULL    |                | 
+--------+------------------+------+-----+---------+----------------+

mysql> desc user;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | 
| company   | varchar(50)         | NO   |     | NULL    |                | 
| street    | varchar(30)         | YES  |     | NULL    |                | 
| zip       | varchar(5)          | YES  |     | NULL    |                | 
| city      | varchar(30)         | YES  |     | NULL    |                | 
| country   | varchar(30)         | YES  |     | NULL    |                | 
| username  | varchar(20)         | YES  |     | NULL    |                | 
| person    | varchar(50)         | YES  |     | NULL    |                | 
| password  | varchar(32)         | YES  |     | NULL    |                | 
| userlevel | tinyint(3) unsigned | NO   |     | NULL    |                | 
| blocked   | tinyint(3) unsigned | YES  |     | NULL    |                | 
| eurochf   | varchar(3)          | YES  |     | NULL    |                | 
| margin    | tinyint(3) unsigned | YES  |     | NULL    |                | 
+-----------+---------------------+------+-----+---------+----------------+


Query:
Code:

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"


Kao sto mozete zakljuciti iz tabele user uzimam parametar margin da bih sa njim pomnozio inputprice i update-ovao outputprice. Tabele nemaju puno slogova i recimo najveca tabela 'temprecord' nece nikad premasiti cifru od 100.000.

Pitanje je : da li je ovaj query mogao bolje da se napishe i koliko ovakav opterecuje bazu buduci da ima 2 subquerija.
Potpis
:: Nemoj se svadjati sa budalom, ljudi cesto nece primjetiti razliku ::
Re: Update tabele koristenjem subquerija 03.06.2009. u 18:40 · pre 211 meseci #2
deluje mi da moze, ali toliko me glava boli ceo dan ... i ova godisnja doba i ... i ...

elem, aj please umesto desc daj show create table .. (pored svega, mnogo lakse za copy paste u lokalnu bazu za testiranje) ... i obavezno daj izlaz od

Code:

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) = "12" AND YEAR(t.starttime) = "2008"


da vidimo sta on uopste sada radi i gde moze da se poboljsa
Potpis
arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/
Re: Update tabele koristenjem subquerija 04.06.2009. u 20:41 · pre 211 meseci #3
Oki, evo ide.. (strukture tabela)

Code:

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `company` varchar(50) NOT NULL,
  `street` varchar(30) default NULL,
  `zip` varchar(5) default NULL,
  `city` varchar(30) default NULL,
  `country` varchar(30) default NULL,
  `username` varchar(20) default NULL,
  `person` varchar(50) default NULL,
  `password` varchar(32) default NULL,
  `userlevel` tinyint(3) unsigned NOT NULL,
  `blocked` tinyint(3) unsigned default NULL,
  `eurochf` varchar(3) default NULL,
  `margin` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1


Code:

mysql> show create table temprecord\G
*************************** 1. row ***************************
       Table: temprecord
Create Table: CREATE TABLE `temprecord` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `from` varchar(20) NOT NULL,
  `to` varchar(20) NOT NULL,
  `starttime` datetime NOT NULL,
  `duration` int(5) unsigned NOT NULL,
  `inputprice` float unsigned NOT NULL default '0',
  `outputprice` float unsigned default '0',
  `invoiceid` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `uniq_cdr` (`from`,`to`,`starttime`,`duration`),
  KEY `id_2` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3340 DEFAULT CHARSET=latin1


Code:

mysql> show create table phonenumber\G
*************************** 1. row ***************************
       Table: phonenumber
Create Table: CREATE TABLE `phonenumber` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `userid` int(10) unsigned default NULL,
  `number` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1


Explain:

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) = "12" AND YEAR(t.starttime) = "2008";
+----+--------------------+-------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | t     | ALL    | NULL          | NULL    | NULL    | NULL | 1653 | Using where | 
|  2 | DEPENDENT SUBQUERY | u     | eq_ref | PRIMARY,id    | PRIMARY | 4       | func |    1 | Using where | 
|  3 | DEPENDENT SUBQUERY | p     | ALL    | NULL          | NULL    | NULL    | NULL |    6 | Using where | 
+----+--------------------+-------+--------+---------------+---------+---------+------+------+-------------+
Potpis
:: Nemoj se svadjati sa budalom, ljudi cesto nece primjetiti razliku ::
Re: Update tabele koristenjem subquerija 08.06.2009. u 08:58 · pre 211 meseci #4
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% :)


Potpis
arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/
Re: Update tabele koristenjem subquerija 10.06.2009. u 02:17 · pre 211 meseci #5
nesto gledam sada ovaj upit ..


Code:

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";


i tu nesto ne valja ...

(SELECT u.margin / 100 FROM `user` as u WHERE u.id = (SELECT userid FROM phonenumber as p WHERE p.number = t.`from`)))

dakle ovde je jednako .. ne "in" sto znaci da
Code:

SELECT userid FROM phonenumber as p WHERE p.number = t.`from`


mora da vrati uvek jedan i samo jedan slog, sto dalje znaci da je `phonenumber`.`number` unique u tabeli sto se iz opisa tabele ne vidi ... tako da je ovaj deo upita u startu "neispravan" .. ili ce polje biti u tabeli unique ili ovde moras nekako da limitiras "koji userid oces" sa na primer max(userid) kao "trulim" resenjem .. posto ne meres da stavis limit 1 unutar subquerija... ako pustis "samo ovaj" upit a ima vse useridova sa istim p.number dobices error da "select ... returns more the one row" ....ali ako ovaj upit postoji "unutar" drugog kao sto je slucaj, ovaj ceo contraption ce vratiti NULL

kako je verovatno u aplikaciji tako da jedan broj ne mere da pripada dvoici useridova problem je samo u nepostojanju unique kljuca, ali sama tabela verovatno ima ispravne podatke .. te mozemo pretpostaviti da je `phonenumber`.`number` unique, onda je ceo taj deo upita bolje napisati

Code:

(SELECT u.margin / 100 FROM `user` u JOIN phonenumber p ON (u.id=p.userid) WHERE  p.number = t.`from`)


te onda u glavni izgleda:

Code:

update temprecord as t SET outputprice = inputprice * (1 + 
  (SELECT u.margin / 100 FROM `user` u JOIN phonenumber p ON (u.id=p.userid) WHERE  p.number = t.`from`)
)
WHERE MONTH(t.starttime) = "12" AND YEAR(t.starttime) = "2008";


sada, opet, ovaj interni ne sme da vraca vise od 1 row .. (meni sa mojim test podacima vraca generalno zbog malopre spomenutog problema) ...

razlika u brzini bi ovde mogla da postoji .. mada ne mora da znaci .. sa mojim test podacima nema razlike .. no, valjalo bi testirati na validnim podacima .. .takodje bi bilo validno dodati taj unique kljuc gde je potreban... no - postoji sansa da ova promena moze znatno da ubrza ceo proces .. tebalo bi probati nad realnim podacima kao sto rekoh ...

elem .. opet ista prica ... inner select vraca "samo jedan" rezultat .. sto opet znaci da to moze da se joinuje :D

Code:

UPDATE temprecord  t  JOIN phonenumber p ON (p.number = t.`from`) JOIN `user` u ON (u.id=p.userid) SET t.outputprice = t.inputprice * (1 + u.margin / 100 )
WHERE MONTH(t.starttime) = "12" AND YEAR(t.starttime) = "2008";


e, sad .. ovaj upit bi trebalo da je "na realnim podacima" sa "pravilnim tabelama" brzi dosta od originala ... nisam siguran da mu uopste treba onaj i_number koji sam dodao ako se doda unique kljuc na
Code:

alter table phonenumber add unique u_number (number);


sve u svemu .. ako postoji neka test baza sa realnim podacima ... probaj :) ..
Potpis
arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/
Re: Update tabele koristenjem subquerija 10.06.2009. u 02:24 · pre 211 meseci #6
btw, ako nekoga zanima sta radi "mysql support engineer" .... ovaj thread je "upravo to" .. dakle preko 60% je nesto ovog tipa .... (obicno dosta kompleksnije ali generalno to) ... ovakva "pomoc" (bukvalno evo ovaj tred) bi bila "naplacena" sa 15-20min konsaltinga. (Enteprise Platinum korisnici dobijaju 8h konsaltinga godisnje po serveru - dakle posle ovako necega bilo bi im oduzeto 15 ili 20min od te sume, posto je to vreme koje sam ja potrosio na ovaj "problem") .. realno bi bilo 15 posto bi korisnik verovatno dao user/pass za njegov server ili uploadovo dump baze pa ne bi ja morao da se drndam da punim dummy podatke .. a bez podataka bi bilo 20 ..
Potpis
arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/
Re: Update tabele koristenjem subquerija 10.06.2009. u 21:35 · pre 211 meseci #7
Zaboravih da ti se zahvalim neki dan. Moram jos par puta procitati ove tvoje zadnje postove pa se javim (a i probam). U sustini nemam veliku kolicinu podataka i necu je nikad ni imati.
Hvala jos jednom.
Potpis
:: Nemoj se svadjati sa budalom, ljudi cesto nece primjetiti razliku ::
Re: Update tabele koristenjem subquerija 10.06.2009. u 21:53 · pre 211 meseci #8
Citat:
bogdan.kecman: btw, ako nekoga zanima sta radi "mysql support engineer" .... ovaj thread je "upravo to" .. dakle preko 60% je nesto ovog tipa .... (obicno dosta kompleksnije ali generalno to) ... ovakva "pomoc" (bukvalno evo ovaj tred) bi bila "naplacena" sa 15-20min konsaltinga. (Enteprise Platinum korisnici dobijaju 8h konsaltinga godisnje po serveru - dakle posle ovako necega bilo bi im oduzeto 15 ili 20min od te sume, posto je to vreme koje sam ja potrosio na ovaj "problem") .. realno bi bilo 15 posto bi korisnik verovatno dao user/pass za njegov server ili uploadovo dump baze pa ne bi ja morao da se drndam da punim dummy podatke .. a bez podataka bi bilo 20 ..


Interesantna informacija ;)
A usput bih i ja htio da ti se zahvalim na ucestvovanju na forumu i zaista odlicnim postovima.
Potpis
Beneath civilization's fragile crust, cold chaos churns...
Re: Update tabele koristenjem subquerija 10.06.2009. u 23:44 · pre 211 meseci #9
misk0, nema na cemu ... probaj na test podacima ... razmisli o tom unique kljucu nad `phonenumber`.`number` poljem ... odradi test na probnim podacima .. ali realno ti bi trebalo da je to ... namerno nisam samo "pljunuo rezultat" vec sam bukvalno prosao ceo misaoni proces kroz ta dva posta, sta sam probao, sta sam primetio i kako sam sta zakljucio .. kapiram da je to najbolji nacin da se ta vestina savlada (riba i pecanje...)

tyler, da, verujem da jeste, zato sam je i ostavio .. za razliku od "klasicne podrske" mysql nema podrsku koja prica hindu, koja voli da prica telefonom, koja od***a klijenta i koja te pita da li si upalio kompjuter, koja ti savetuje da odradis power cycle na produkcionom serveru i slicno :) ... pogledaj:
http://www.mysql.com/products/enterprise/features.html

kapiram da svako iz taka pomisli da kada uzme mysql support dobije telefon nekih indusa :) ...
Potpis
arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/
Odgovori
Prijavite se da biste odgovorili u ovoj temi.PrijavaRegistracija

Premeštanje teme