Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: Sql, statment and leftjoins and indexes...

    Select * from tmaster a left join tdetail b on b.idetailmastnum=a.imasterid left join tcatmar c on b.idetailmar=c.icatmarid left join tcatvar d on b.idetailvar=d.icatvarid left join tcatsal e on b.idetailsala=e.icatsalid left join tcatfri f on a.imasterfrig=f.icatfriid left join tcatclie g on a.imasterclie=g.icatclieid where a.dmasterfecha>='2002/01/01' and a.dmasterfecha<='2002/06/01' and a.imasterclie=1 order by idetailmastnum,idetailnreng

    in tmaster only have 6 records, in tdetail mmm 30 records in all table so so 5 records why are slow slow? what indexes recommend me for create?


    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all tables should have a primary key

    that will give them an index automatically

    then, for related tables, declare an index for each foreign key (which, unless you're using innodb, you don't need to bother declaring as a foreign key) -- the foreign key being the column in the related table that connects to the first table's primary key

    thus, every column in all of your ON conditions will have an index

    then, consider an index for a column such as dmasterfecha which is used in the WHERE clause and has good selectivity -- imasterclie, on the other hand, may not have good selectivity

    by good selectivity, i mean there is a good chance that the optimizer will decide to use the index because it reduces the number of potential rows to be returned

    for example, if you had a large table of people, and you had a column for gender, with 1=male and 2=female, then fully half the table will have each value, so that does not provide good selectivity

    since the database requires two reads, one for the index and one for the row, if you access half the table that way, there's not much point, so a good optimizer will ignore an index on gender


    rudy
    http://r937.com/

  3. #3
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    You should use explain statement to know how index are unsed in your statement

    Code:
    explain Select * from tmaster a left join tdetail b on b.idetailmastnum=a.imasterid left join tcatmar c on b.idetailmar=c.icatmarid left join tcatvar d on b.idetailvar=d.icatvarid left join tcatsal e on b.idetailsala=e.icatsalid left join tcatfri f on a.imasterfrig=f.icatfriid left join tcatclie g on a.imasterclie=g.icatclieid where a.dmasterfecha>='2002/01/01' and a.dmasterfecha<='2002/06/01' and a.imasterclie=1 order by idetailmastnum,idetailnreng
    You should also use colname instead of *
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  4. #4
    Join Date
    Jul 2003
    Posts
    2

    i have solved my problem, but....

    i work with mysql + delphi + Zeos Componets when i execute these query in EMS mysql manager work fien fast fastor in sql explorer too, but in my code of delphi slow slow with TZquery of Zeos, but when i change the component by a Native BDE Tquery work fast fast same to Ems mysql explorer, anyone her eknow why?



    thanks


    regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •