List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 30 1999 8:42pm
Subject:Keys & Full-Table Scan
View as plain text  
>>>>> "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

Keys & Full-Table ScanDan Ray16 Jul
  • Keys & Full-Table ScanMichael Widenius31 Jul