On 4/20/05, Dan Nelson <[EMAIL PROTECTED]> wrote: > In the last episode (Apr 20), Willie Gnarlson said: > > I have been attempting to uncover a fast method for retrieving unique > > items from every column in a table. The table has approximately 60 > > thousand rows. Each row has an index associated with it, and running > > 'explain select distinct class from dlist' shows that it is using the > > index. > > > > However, since there are 24 columns, running that select query for > > each column takes about 4 long seconds on a P4 2.4 Ghz. > > If there are a significant number of identical values (ages, sizes, > etc), then setting PACK_KEYS=1 on the table may help by making smaller
There are, but PACK_KEYS=1 seemed to shed about a second (just guessing, but it did seem only slightly faster) off the total query time. > indexes. You might also be able to convert some columns to ENUMs, > which will make their indexes even smaller (and let your SELECT be > almost instantaneous if mysql knows how to optimize it right :) One column was already ENUM, but it wouldn't be possible to make any others that type. > Another alternative would be to build a table for each column, holding > the output of SELECT DISTINCT(column) FROM dlist. Rebuild it as > frequently as needed. Okay, I decided to try that option, as it sounded like a good one. I created the tables, and since the main table would be rarely updated I was going to run DELETE FROM on the column tables and run the queries `INSERT INTO table (col) SELECT DISTINCT col FROM dlist' each time a new record was updated or added. It turns out that is almost as slow as the original method. So then I added an index to every column table, and it was still no better. I thought maybe generating a select box for each column's contents was the problem, since unique items in some tables are as much as 4 thousand, but singling out the code which performs the queries, eliminating the select box generation, confirmed that the queries were indeed the slow part. I may have to abandon the retrieving of unique items from every column in a table unless you or anyone else have any other ideas. Thanks for your help, Dan. -- Willie Gnarlson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
