Re: joinAdd question - SOLVED

Justin Patrin escreveu:

On Wed, 08 Dec 2004 11:13:03 -0200, Jacques <iacques@yahoo.com.br> wrote:

Justin Patrin escreveu:

On Wed, 08 Dec 2004 05:55:45 -0200, Jacques <iacques@yahoo.com.br> wrote:

It´s the folow: In this query, i get 5000 rows from 1o reg, ok. The benchmark is +- 7 seconds; : )

      $db = new DataObject_Frm_usuario();
      $total = $db->count();
      $db->limit(1  ,  5000);
      $cargo = new DataObject_Srh_cargo();
      $db->joinAdd($cargo);
      $lotacao = new DataObject_Srh_lotacao();
      $db->joinAdd($lotacao);

In this query, i get only ONE (1) row from 1o reg ok. The benchmark is +- 7 seconds; : |

      $db = new DataObject_Frm_usuario();
      $total = $db->count();
      $db->limit(5000  ,   1);
      $cargo = new DataObject_Srh_cargo();
      $db->joinAdd($cargo);
      $lotacao = new DataObject_Srh_lotacao();
      $db->joinAdd($lotacao);

And if query start from 15.000 the time is +- 18 seconds.... : (

      $db = new DataObject_Frm_usuario();
      $total = $db->count();
      $db->limit(15000  ,   1);
      $cargo = new DataObject_Srh_cargo();
      $db->joinAdd($cargo);
      $lotacao = new DataObject_Srh_lotacao();
      $db->joinAdd($lotacao);

Why this happens? I don´t use the Pager with joinAdd? Thanks for any help!

Well, it does take time for the DB to select all of the records and seek to the point where you want to go.... Try turing on debugging and running the queries manually. See if the queries take the same amount of time.

You have reason, the error you are not with data_object therefore delay you occur in mysql. With one join the performance is fast, however with more than one it falls frightfully...

I'm not sure what you mean here. Are you saying that you tried my suggestion and when joining in mysql it was as slow as with DB_DataObject? If this is the case, try adding an index to the fields you're joining to. JOINs do take a long time...

The problem was solved. I added some index and the performance now is very, very fast, also with 4 JOINS! I am thankful for the support, dear Justin! Tip: If anybody have performance problems with JOINS and MySql, try the command: EXPLAIN SELECT * FROM... This show many info about your query that can be improved with few steps... More info in: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Grateful, Jacques, from Brazil!