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 2 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]
