>>>>> "Dan" == Dan Ray <dan@stripped> writes:
Dan> Hey, all--
Dan> One more question and I'm done for the time being.
Dan> At one point on a site I'm working on I "SELECT DISTINCT
Dan> Department,Department_no FROM Products". Department is a varchar(100),
Dan> Department_no is an int(5). Products contains just over 20k records, and
Dan> this query takes 8-10 seconds--just long enough for a web user to start to
Dan> wonder whether the site is dead.
Dan> Here's my question. Given that SELECT DISTINCT without a WHERE obviously
Dan> requires a full-table scan, will making Department a key help my speed any?
It will if you make it CHAR BINARY. In MySQL 3.23 you don't have this
restrictions of your are using MyISAM tables.
Dan> At other places in the site where I "SELECT DISTINCT Class FROM Products
Dan> WHERE Department_no = xx", it's pretty quick because Department_no is an int
Dan> and a key... It's just that first page that lags.
Dan> Is "SELECT DISTINCT" inherantly slow? Would I be better off selecting from
Dan> my whole table and "distinguising" in Perl by passing my results through a
Dan> hash? Any other thoughts?
In MySQL 3.22 some SELECT DISTINCT queries are slower than others (In
particular if you have a TEXT/BLOB in the result).
MySQL 3.23 doesn't have this problem.
SELECT DISTINCT should however be faster than a Perl parsing in any