nandix / mydbsuggest (http://fernandoipar.com/mydbsuggest/)

Provides suggestions for optimizing MySQL databases.

Clone this repository (size: 8.3 KB): HTTPS / SSH
$ hg clone http://bitbucket.org/nandix/mydbsuggest/
commit 5: 6f3565a51697
parent 4: a79d8fb41111
branch: default
Banging my head against the wall because I want to do everything without cursors, well, it outputs garbage, and it takes forever. Welcome cursors
Fernando Ipar
11 months ago

Changed (Δ236 bytes):

raw changeset »

sql/extended_procedure_analyse.sql (4 lines added, 4 lines removed)

Up to file-list sql/extended_procedure_analyse.sql: 

1
 
1
 
/*
 
2
 

        	

			
        
			
        	

          		
3

          		
2

          		
extended_procedure_analyse.sql

        	

			
        
			
        	

          		
4

          		
3

          		
(C) 2009 Fernando Ipar - Federico Silva.

        	

			
        
			
        	

          		
5

          		
4

          		
Issues: http://bitbucket.org/nandix/mydbsuggest/issues/

        	

			
        

      

        
			
			

				……
				
					@@ -55,10 +54,11 @@ begin
				
			

			
        
			
        	

          		
55

          		
54

          		
	prepare myStmt from @qry;

        	

			
        
			
        	

          		
56

          		
55

          		
	execute myStmt using @dbName,@tbName;

        	

			
        
			
        	

          		
57

          		
56

          		
	

        	

			
        
			
        	

          		
58

          		

          		

        	

			
        
			
        	

          		

          		
57

          		
	

        	

			
        
			
        	

          		

          		
58

          		
	

        	

			
        
			
        	

          		
59

          		
59

          		
	create temporary table tmp_pao as select * from procedure_analyse_output;

        	

			
        
			
        	

          		
60

          		

          		
	prepare myStmt from 'update tmp_pao set Indexed = "Overindexed" where exists (select Field_name,index_name,count(index_name) from procedure_analyse_output pao inner join information_schema.statistics s on pao.Field_name = s.column_name where table_schema = ? and table_name = ? and seq_in_index = 1 and pao.Field_name = tmp_pao.Field_name group by index_name having count(index_name) > 1)';

        	

			
        
			
        	

          		
61

          		

          		
	execute myStmt  using @dbName,@tbName;

        	

			
        
			
        	

          		

          		
60

          		
	prepare myStmt from 'update tmp_pao set Indexed = "Overindexed" where exists (select column_name from information_schema.statistics where table_schema = ? and table_name = ? and column_name = tmp_pao.Field_name and seq_in_index = 1) and not exists (select count(distinct column_name) from information_schema.statistics where table_schema = ? and table_name = ? and column_name != tmp_pao.Field_name and index_name in (select index_name from information_schema.statistics where table_schema = ? and table_name = ? and column_name = tmp_pao.Field_name) having count(distinct column_name) > 1)';

        	

			
        
			
        	

          		

          		
61

          		
	execute myStmt  using @dbName,@tbName,@dbName,@tbName,@dbName,@tbName;

        	

			
        
			
        	

          		
62

          		
62

          		
		

        	

			
        
			
        	

          		
63

          		
63

          		
	select * from tmp_pao;

        	

			
        
			
        	

          		
64

          		
64