[jamiguel77]

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

[r937]

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/

[omiossec]

You should use explain statement to know how index are unsed in your statement

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 *

[jamiguel77]

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