Hi.
On Sun, Sep 09, 2001 at 10:52:49AM -0400, [EMAIL PROTECTED] wrote:
> It would be better to use joins. Something like this:
Sorry? He did already use joins. You use LEFT JOINs, which have a
different meaning! So your query may give a different result as
desired. Additionally, LEFT JOINs are usually slower than normal joins
(written with comma).
> SELECT swimmers.surname_swimmer , swimmers.name_swimmer , swimmers.born ,
> results.result , results.scale , competitions.date_competition
> FROM results
> left join disciplines on results.sif_discipline = disciplines.sif_discipline
> left join swimmers on results.sif_swimmer = swimmers.sif_swimmer
> left join competitions on results.sif_swimmer = swimmers.sif_swimmer
> WHERE results.scale < $no_of_outputs
> AND disciplines.gender = '$gender'
> AND disciplines.pool = $pool_length
> AND disciplines.discipline = '$discipline'
> On Mon, Sep 10, at 04:37pm, Grega ([EMAIL PROTECTED]) wrote:
> >
Grega, could you post the result of "EXPLAIN <your select>"?
Note that KEY(sif_swimmer) is redundant if you already have PRIMARY
KEY (sif_swimmer), but this does only little harm. The same is true
for the other tables.
Maybe an additional key on disciplines could be helpful, but I doubt
that this has a huge impact as the table is rather small.
Btw, how many rows do you expect the result set to have?
[...]
> > disciplines:
> > sif_discipline int(8)
> > discipline varchar(7)
> > gender char(1)
> > pool int(8)
> >
> > PRIMARY KEY (sif_discipline)
> > ________________________________________
> > swimmers:
> > sif_swimmer int(11)
> > surname_swimmer text
> > name_swimmer text
> > gender_swimmer text
> > born year(4)
> >
> > KEY (sif_swimmer),
> > PRIMARY KEY (sif_swimmer)
> > ________________________________________
> > results:
> > sif_swimmer int(11)
> > sif_competition int(11)
> > sif_discipline int(11)
> > result text
> > scale smallint(6)
> >
> > PRIMARY KEY (sif_swimmer, sif_competition, sif_discipline),
> > KEY (sif_swimmer),
> > KEY (sif_discipline)
> > ________________________________________
> > competitions:
> > sif_competition int(11)
> > date_competition datetime
> >
> > KEY (sif_competition),
> > PRIMARY KEY (sif_competition)
> >
> > the sql query i have written:
> >
> > SELECT swimmers.surname_swimmer , swimmers.name_swimmer , swimmers.born ,
> > results.result , results.scale , competitions.date_competition
> > FROM results , disciplines , swimmers , competitions
> > WHERE results_scale < $no_of_outputs
> > AND disciplines.gender = '$gender'
> > AND disciplines.pool = $pool_length
> > AND disciplines.discipline = '$discipline'
> > AND results.sif_swimmer = swimmers.sif_swimmer
> > AND results.sif_discipline = disciplines.sif_discipline
> > AND results.sif_competition = competitions.sif_competition
> >
> > now, this query executes for about 10 minutes on an AMD 900/256MB and then i
> > get an "execution time exceeded" from PHP
Ever tried it in the command line client?
> > could anyone help me with writing a better query?
The query itself looks fine. It's rather that the database can need
some indexes or the MySQL server is not so well tuned.
> > maybe, the tables in the database are to big? results - 25000 entries,
> > competitions - 400 entries, swimmers - 6000 entries, disciplines - 100
> > entries
> > but probably, thats not the problem, is it? :)
It depends. In the worst case, a join has to examine
25000*400*6000*100 = 6.000.000.000.000 rows, which may take some
time. :-)
Normally this number is greatly reduced by the use of indexes. So
let's see the EXPLAIN and then we can tell a lot more (at least I hope
so ;).
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
