[ always cc the list so others can share the fix or make appropriate
comments ]
Nicholas Wyatt wrote:
hi chris,
thanks for answering! however, i do already have indexes on those
columns. all my tables use the myisam storage engine. what are the
differences you mentioned between these engines when using MIN()?
myisam is a non-transactional table type, so only 1 client can
update/delete/insert into it at once (other clients attempting to do
this will be locked until the insert/update/delete finishes). That means
the table itself can keep information about the largest value, the
smallest value and the number of rows the table has.
innodb is a transactional table type, which means many clients can
update/delete/insert into it at once. That means the table cannot keep
information about values and the number of rows because at any time it
could change in another client.
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
i did find a way to speed up the query, by the way. i joined the tables
the other way around. the query now takes about 0,5 seconds.
SELECT site.site_id, site.site_title, site_url, site.site_testtype,
site.project_id, test.test_kt_points,
MIN(test_item.ti_evaluation) as completed,
IF (site.site_date < site.site_enddate, site.site_enddate,
site.site_date) AS sort_date
FROM test_item
LEFT JOIN test ON (test.test_id = test_item.test_id)
LEFT JOIN site ON (site.site_id = test.site_id)
WHERE site.site_testtype IN (-1, 0, 1, 2)
GROUP BY site.site_id
Interesting.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
