Also note that the server is pretty fast, dual 2 GHz with 2GB of RAM. Using the "my-huge.cnf" settings.
>Also... I forgot to mention [EMAIL PROTECTED] I'm using: >mysql 4.0.20-standard-log Official MySQL RPM > >And here is the EXPLAIN on the select portion of the slow query is: >+-------+--------+----------------------------------------+-------------------+---------+ >| table | type | possible_keys | key | >key_len | >+-------+--------+----------------------------------------+-------------------+---------+ >| AL | ref | PRIMARY,CAT_ATTR_INDEX,ID_VAL_ID_INDEX | CAT_ATTR_INDEX | >8 | >| A | ref | PRIMARY,ATTR_VAL_ID_INDEX | ATTR_VAL_ID_INDEX | >4 | >| AC | eq_ref | PRIMARY | PRIMARY | >8 | >+-------+--------+----------------------------------------+-------------------+---------+ > >(continued..) >+-------+---------------------+-------+----------------------------------------------+ >| table | ref | rows | Extra | >+-------+---------------------+-------+----------------------------------------------+ >| AL | const,const | 14077 | Using where; Using temporary; Using filesort | >| A | AL.attr_val_id | 1 | | >| AC | A.attr_val_id,const | 1 | | >+-------+---------------------+-------+----------------------------------------------+ > > >Thanks >- John > >>I have a very slow query that is taking anywhere from 10 to 90 seconds. The query >>selects attribute values that are associated with a particular attribute and >>category and orders them alphabetically. >> >>For example, category 1 might have an attribute >>Example: >>category: books >>category_id: 1 >>attr_name: authors >>attr_id: 5 >>------------- >>OUTPUT (attribute.attr_val, attribute.attr_val_id) >>Stephen King, 123 >> >>In order to associate a attribute with a category, I have a table "attr_lookup" >>which references an attr_val_id, attr_id to a particular category. >> >>Since I am populating a combobox and some categories have 4 to 6,000 authors, I need >>to limit the results by the most popular attr_val (the most popular authors in this >>case). I decided to make a denormalized table, attribute_count(attr_val_id, >>attr_count) that keeps track of the attribute count. >> >>I tried a temporary table because I thought the sorting would be faster this way but >>it takes 10 to 95 seconds: >> >>CREATE TEMPORARY TABLE tmpAttr (KEY(attr_val_id)) >>SELECT A.attr_val_id, A.attr_val >>FROM attr_lookup AS AL >>INNER JOIN attribute AS A ON A.attr_val_id = AL.attr_val_id >>INNER JOIN attribute_count AS AC ON AC.attr_val_id = A.attr_val_id AND AC.attr_id = >>AL.attr_id >>WHERE AL.attr_id = 5 AND AL.category_id = 1 >>ORDER BY AC.attr_count DESC LIMIT 200 >> >>Using the temporary table, I then select from the top authors in alphabetical order >>(this query is fast - no problems here) >> >>SELECT TA.attr_val, TA.attr_val_id >>FROM tmpAttr AS TA >>ORDER BY TA.attr_val ASC LIMIT 200 >> >> >>Any help or suggestions on this would be wonderful! >>- John >> >> >> >> >>Summary: >>OBJECTIVE: >>Select authors (attribute values) for a category limited to most popular 200 authors >>(attribute values) and then order alphabetically. >>--------------------------------- >>mysql> DESCRIBE attribute; >>+-------------+---------+------+-----+---------+----------------+ >>| Field | Type | Null | Key | Default | Extra | >>+-------------+---------+------+-----+---------+----------------+ >>| attr_val_id | int(13) | | PRI | NULL | auto_increment | >>| attr_val | text | YES | MUL | NULL | | >>+-------------+---------+------+-----+---------+----------------+ >>(123,Stephen King) >> >>mysql> DESCRIBE attr_lookup; >>+--------------+---------+------+-----+---------+-------+ >>| Field | Type | Null | Key | Default | Extra | >>+--------------+---------+------+-----+---------+-------+ >>| attr_id | int(9) | | PRI | 0 | | >>| attr_val_id | int(13) | | PRI | 0 | | >>| category_id | int(9) | | PRI | 0 | | >>+--------------+---------+------+-----+---------+-------+ >>(5,123,1) #Author Stephen King is in category 1 (Horror Books) >> >>mysql> DESCRIBE attribute_count; >>+-------------+---------+------+-----+---------+-------+ >>| Field | Type | Null | Key | Default | Extra | >>+-------------+---------+------+-----+---------+-------+ >>| attr_val_id | int(13) | | PRI | 0 | | >>| attr_id | int(9) | | PRI | 0 | | >>| attr_count | int(9) | | | 0 | | >>+-------------+---------+------+-----+---------+-------+ >>(123,5,35) #Author Stephen King occurs 35 times >> >>// following table isn't used in above queries but it helps to see it >>mysql> DESCRIBE compare_attr; >>+-----------+-------------+------+-----+---------+----------------+ >>| Field | Type | Null | Key | Default | Extra | >>+-----------+-------------+------+-----+---------+----------------+ >>| attr_id | int(9) | | PRI | NULL | auto_increment | >>| attr_name | varchar(60) | YES | MUL | NULL | | >>+-----------+-------------+------+-----+---------+----------------+ >>(5,Authors) >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
