Izvelk vecākos nevis jaunākos!

[Aleksejs]

Šitas nav labi:

156 | Using where; Using temporary; Using filesort

un arī šitas nav labi:

156 | Using where

 

Pirmais ir īpaši nelabi :)

[Gints Plivna]

OK patiesībā vēlme it kā ir triviāla tai pašā laikā to nemaz nav tik viegli realizēt. Kaut vai padomājot kā to cilvēks darītu.

Parakājos mazliet pa MySQL dokumentāciju un secināju, ka tam līdz dažām advancētākām datubāzēm vēl traki tāls ceļš ejams ;)

Pie indeksiem asc desc var rakstīt bet indeksus vienalga taisa ascending jebkurā gadījumā, izskatās, ka tādu index range scan descending arī viņš nesaprot tā kā diemžēl jau pati pamata problēma atrast pēdējos komentārus n dažādiem rakstiem rakstam atdurās MySQL nespējībā :( Vismaz tā man izskatās no dokiem.

Ok uzstādam testa vidi:

 

create table komentari (
 kom_id int not null primary key, 
 kom_rks_id int not null, 
 kom_ltt_id int not null, 
 kom_dat datetime
);
insert into komentari values (1, 1, 1, now());
insert into komentari values (2, 1, 2, now());
insert into komentari values (3, 1, 3, now());
insert into komentari values (4, 2, 3, now());
insert into komentari values (5, 2, 2, now());
insert into komentari values (6, 3, 1, now());
create table raksti (
 rks_id int not null primary key,
 rks_nosaukums varchar(10)
);
insert into raksti values (1, 'pirmais');
insert into raksti values (2, 'otrais');
insert into raksti values (3, 'treshais');
create table lietotaji (
 ltt_id int not null primary key, 
 ltt_niks varchar(10)
);
insert into lietotaji values (1, 'niknais');
insert into lietotaji values (2, 'joho');
insert into lietotaji values (3, 'kuku');

 

Risinam probzu soli pa solim. Palasot par to kā MySQLs optimizē group by klauzas var atrast kādā veidā jāveido indekss, lai rezultāts būtu cik vien iespējams MySQLā labs.

 

create unique index k_idx on komentari(kom_rks_id, kom_id);
explain
select kom_rks_id, max_kom_id from (
 select kom_rks_id, max(kom_id) max_kom_id
 from komentari
 group by kom_rks_id
) a
order by max_kom_id desc
limit 2;
+----+-------------+------------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table	  | type  | possible_keys | key   | key_len | ref  | rows | Extra					|
+----+-------------+------------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | PRIMARY	 | <derived2> | ALL   | NULL		  | NULL  | NULL	| NULL |	3 | Using filesort		   |
|  2 | DERIVED	 | komentari  | range | NULL		  | k_idx | 4	   | NULL |	3 | Using index for group-by |
+----+-------------+------------+-------+---------------+-------+---------+------+------+--------------------------+

 

Tātad kā var redzēt no operāciju skaidrojuma, tad Using index for group-by ir tas labākais ko priekš group by var izspiest.

 

Nu un tālāk jau atliek tikai pievienot klāt pārējas tabulas. Šeit man nav saprotams, kāpēc izmantojot inner join tiek ģenerēts būtiski sliktāks plān nekā izmantojot left join, bet nu labi ko darīt...

explain
select max_kom_rks_id, max_kom_id, kom_dat,
   rks_nosaukums, ltt_niks
from (
 select kom_rks_id max_kom_rks_id, max(kom_id) max_kom_id
 from komentari
 group by kom_rks_id
) der_tbl
left join komentari
 on (max_kom_id = kom_id and max_kom_rks_id = kom_rks_id)
left join raksti
 on (max_kom_rks_id = rks_id)
left join lietotaji
 on (kom_ltt_id = ltt_id)
order by max_kom_id desc
limit 2;
+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+--------------------------+
| id | select_type | table	  | type   | possible_keys | key	 | key_len | ref					   | rows | Extra					|
+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+--------------------------+
|  1 | PRIMARY	 | <derived2> | ALL	| NULL		  | NULL	| NULL	| NULL					  |	3 | Using filesort		   |
|  1 | PRIMARY	 | komentari  | eq_ref | PRIMARY,k_idx | PRIMARY | 4	   | der_tbl.max_kom_id		|	1 | Using where			  |
|  1 | PRIMARY	 | raksti	 | eq_ref | PRIMARY	   | PRIMARY | 4	   | der_tbl.max_kom_rks_id	|	1 |						  |
|  1 | PRIMARY	 | lietotaji  | eq_ref | PRIMARY	   | PRIMARY | 4	   | test.komentari.kom_ltt_id |	1 |						  |
|  2 | DERIVED	 | komentari  | range  | NULL		  | k_idx   | 4	   | NULL					  |	3 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+--------------------------+

 

Protams, ka beigās jau nu tie ieraksti ir jāsakārto. Šeit gan man nav īsti saprotams, iespējams, ka MySQLs vispirms taisa visus joinus un tad tikai kārto un atlasa tos 2 ierakstus, ko man vajag dēļ limit. Tas daudz ierakstu gadījumā būtu reāls bezsakars, tāpēc iespējams ir vērts izveidot vēl vinu starplīmeni, kura atlasam tikai tos n ierakstus:

explain
select max_kom_rks_id, max_kom_id, kom_dat, 
   rks_nosaukums, ltt_niks 
from (
 select max_kom_rks_id, max_kom_id
 from (
select kom_rks_id max_kom_rks_id, max(kom_id) max_kom_id
from komentari
group by kom_rks_id
 ) der_tbl1
 order by max_kom_id desc
 limit 2
) der_tbl2
left join komentari 
 on (max_kom_id = kom_id and max_kom_rks_id = kom_rks_id)
left join raksti 
 on (max_kom_rks_id = rks_id)
left join lietotaji 
 on (kom_ltt_id = ltt_id);
+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+--------------------------+
| id | select_type | table	  | type   | possible_keys | key	 | key_len | ref					   | rows | Extra					|
+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+--------------------------+
|  1 | PRIMARY	 | <derived2> | ALL	| NULL		  | NULL	| NULL	| NULL					  |	2 |						  |
|  1 | PRIMARY	 | komentari  | eq_ref | PRIMARY,k_idx | PRIMARY | 4	   | der_tbl2.max_kom_id	   |	1 | Using where			  |
|  1 | PRIMARY	 | raksti	 | eq_ref | PRIMARY	   | PRIMARY | 4	   | der_tbl2.max_kom_rks_id   |	1 |						  |
|  1 | PRIMARY	 | lietotaji  | eq_ref | PRIMARY	   | PRIMARY | 4	   | test.komentari.kom_ltt_id |	1 |						  |
|  2 | DERIVED	 | <derived3> | ALL	| NULL		  | NULL	| NULL	| NULL					  |	3 | Using filesort		   |
|  3 | DERIVED	 | komentari  | range  | NULL		  | k_idx   | 4	   | NULL					  |	3 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------+---------+---------------------------+------+--------------------------+

 

Nu bet vispār uz visu šito skatoties man tā vien šķiet, ka daudz datu gadījumā un daudz lietotāju pieprasījumu gadījumā būtu krietni izdevīgāk izveidot pēdējo 5 komentāru atvasinātu tabuliņu, kurā bāž iekšā tos pēdējos ierakstus un no turienes arī lasa.

 

Gints Plivna

http://datubazes.wordpress.com

[Gints Plivna]

Jā un vēl gribēju piebilst pāris lietas:

1) lai nebūtu jāskanē cauri visa tabula iespējams ir vērts padomāt, ka komentārus meklē tikai teiksim pa pēdējo nedēļu vai pēdējo mēnesi un no tiem attēlo to visu drazu. Tad, protams, sākotnējā ideja drusku pamainas un ir jābūt kādam indeksam uz komentāru datumiem, lai efektīvi varētu atlasīt tikai jaunākos komentārus.

2) tā kā es nesen uzcepināju rakstiņu par indeksiem ;), tad atļaušo to pareklamēt un pabrīdināt, ka katrs indekss var dot kādu labumu, var arī nedot, bet pilnīgi dorši nesīs sliktumu.

[Pentiums]

Liels paldies! :)