Definitely something wrong with your setup, if a query takes as long as 2
hours. I could see maybe 2 seconds to return all 31000 rows, but why would
you ever need all 31000 rows? Put a limit on it to say 20 or 30 and you'll
get fast performance. If your columns are indexed, the order by won't take
as long as you indicate.
As for joins, mysql is very fast in my experience. Take this for instance,
operating on several tables with about 20000 rows:
select a.description as project, b.phase, c.worktype, d.description,
d.regular, d.ot, d.uid, d.sync, d.ot15, d.ot2, d.project_id, d.phase_id,
d.worktype_id, DATE_FORMAT(d.date, '%a, %b %d, %Y') as date from project a,
phase b, worktype c, hours d where week(d.date) = week('2001-02-03') and
year(d.date) = year('2001-02-03') and d.project_id = a.code and d.phase_id =
b.code and d.worktype_id = c.code and d.personnel_id = 1139 order by d.date
asc;
22 rows in set (0.07 sec)
A count for 1 col on the same table takes 0.01 seconds. This is on a slower
machine than yours - but for the join it's only returning the important
rows. I think that might be where you're interpreting the slow performance,
unless you're missing indexes on the pertinent joins.
Julian
--
Julian Wood
Multimedia Developer
University of Calgary
on 3/1/01 6:52 AM, Chris Lee at [EMAIL PROTECTED] wrote:
> on a 45mb table with 31,470 rows mysql takes this long.
>
> mysql> SELECT stockno from products;
> ...
> 31470 rows in set (2.34 sec)
> mysql>
>
> not exactly great performance, if I put two 'LIKE' statments and an 'ORDER BY'
> clause then we're at 8 sec
>
> this is on a PII500 512mb ram linux.mysql 3.23.28 this is not a lightning fast
> server, but I am not impressed with mysql speed. if you ever have todo any
> table joins I would highly recommend against mysql
>
> people has 161 rows.
> products has 31,470 rows
>
> select count(stockno), count(customernum) from products, people;
> ...
> 1 row in set (12.34 sec)
> mysql>
>
> try adding a third table, oi. or a fouth, I timedout at nearly 2 hours with
> four tables. postgres (7.1beta) could do the same four tables in less then
> 0.9sec.
>
> I use mysql because I use mysql. I would like to use postgres but everyone
> seems to use mysql and clients know the name. they request it. I hear problems
> about un-stability of 7.0 on phpbuilder.com, are they warented? I am
> interested in hearing strong customer testomonials for postgres or against it.
> I have little info other then a few benchamrks I ran myself.
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
