Secret to Tuning InnoDB tables for more speed in select's

[eduardosic]

Hi Friend's reading the mysql Help i found this in

"14.2.11. InnoDB Performance Tuning Tips"

* Unlike MyISAM, InnoDB does not store an index cardinality value in its tables. Instead, InnoDB computes a cardinality for a table the first time it accesses it after startup. With a large number of tables, this might take significant time. It is the initial table open operation that is important, so to “warm up” a table for later use, you might want to use it immediately after start up by issuing a statement such as SELECT 1 FROM tbl_name LIMIT 1.

After executing what the SQL Statment the queries they are extremely fast

based in this information i made a simple component base in MyDac 5

source in
http://www.drdsistemas.com/util/Optimizer.zip

I wait that it helps to the all comom helped me.

with this the InnoDB is fast when the MyISAM.

please, try and report result's.

best regard's.

[ben]

hello,

i dont know about speed (cause I didnt see any visible difference) but here are some tips to improve your component:

1) You forgot the "port" property

2) Do not use server, port, username, password. Use a TMyConnection instead as:

Code: Select all

  published
    property Connection: TMyConnection read FConnection write FConnection;

3) Do not create your own query to get table names, you can have MyConnection do it for you:

Code: Select all

var
  TableNames: TStringList;
...

  TableNames := TStringList.Create;
  FConnection.GetTableNames(TableNames);
  for i := 0 to TableNames.Count-1 do
...

4) Do not create SQL query using "#13;". Use SQL.Add... instead as:

Code: Select all

  // in your loop
  QryOptimize.SQL.Add('SELECT 1 FROM...

5) there is no need to Close query after Execute. You close only after Open.

6) I would prefer to execute the queries one by one by Open like:

Code: Select all

  for i := 0 to TableNames.Count-1 do
  begin
    Optimizer.Close;
    Optimizer.SQL.Text := 'SELECT 1 FROM ' + TableNames.Strings[i] + ' LIMIT 1;';
    Optimizer.Open;
  end

instead of Executing one big query.

Hope that helps.

[eduardosic]

hello,

i dont know about speed (cause I didnt see any visible difference) but here are some tips to improve your component:

1) You forgot the "port" property

2) Do not use server, port, username, password. Use a TMyConnection instead as:

Code: Select all

  published
    property Connection: TMyConnection read FConnection write FConnection;

3) Do not create your own query to get table names, you can have MyConnection do it for you:

Code: Select all

var
  TableNames: TStringList;
...

  TableNames := TStringList.Create;
  FConnection.GetTableNames(TableNames);
  for i := 0 to TableNames.Count-1 do
...

4) Do not create SQL query using "#13;". Use SQL.Add... instead as:

Code: Select all

  // in your loop
  QryOptimize.SQL.Add('SELECT 1 FROM...

5) there is no need to Close query after Execute. You close only after Open.

6) I would prefer to execute the queries one by one by Open like:

Code: Select all

  for i := 0 to TableNames.Count-1 do
  begin
    Optimizer.Close;
    Optimizer.SQL.Text := 'SELECT 1 FROM ' + TableNames.Strings[i] + ' LIMIT 1;';
    Optimizer.Open;
  end

instead of Executing one big query.

Hope that helps.

Hi Ben, thank's, I go to make the modifications

the difference of the performance is seen in big tables where the search is made mainly in fields varchar()

before making the optimization the first query is slow therefore the same one still is not in cache,

after the optimization any query is very fast.

I go to create a demonstration and to place here...

thank's