It would be better to use joins. Something like this:
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'
I doubt if this exact query would work best for you so if it is sub-optimal,
read the mysql documentation on joins.
On Mon, Sep 10, at 04:37pm, Grega ([EMAIL PROTECTED]) wrote:
> hello
>
> i have a question regarding a mySQL query sentence:
>
> i have a mysql database named "club" with tables "disciplines", "results",
> "Swimmers" and "competitions"
>
> columns in tables are:
>
> 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)
>
>
>
> variables, passed by PHP:
> izpis.php?gender=M&no_of_outputs=10&pool_length=25&discipline=50K
>
> 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
>
> could anyone help me with writing a better query?
> i know this one is not so good :)
>
> 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? :)
>
> i guess a better query would solve my problem
>
> Thanks in advance!
>
> Grega Lebar
>
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
