Is there a composite index on (master_id, ticket_id)? Since your queries are selecting on a particular master_id, and ordering by ticket_id, along with the limit I think MySQL would be able to use such an index in an optimization.
-Daniel -----Original Message----- From: Terence [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 10:52 PM To: mysql@lists.mysql.com Subject: performance on single column index with few distinct values Hi list, I have run into problems on a master table for our helpdesk. We have the following table: ticket_id (int) - autoincrement (indexed) master_id (int) (indexed) Master ID is used to distinguish multiple helpdesks. In this table there are 100k records, but only 10 distinct master_id's. For example: ticket_id master_id 1 1 2 1 3 2 4 2 5 3 ... ... When trying to do pagination I use the following SQL: SELECT ticket_id FROM my_table WHERE master_id = '1' ORDER BY ticket_id DESC LIMIT 0,10 The problem is that there are 20k records where master_id = 1, so the lookup is pretty slow especially when I start joining other tables. When joining other tables the query gets slower and slower, I guess because the lookups on joining tables result in fewer rows being joined when using EXPLAIN. SELECT * FROM helpdesk_tickets ht, helpdesk_category_master hcm, helpdesk_sub_category_master hscm WHERE ht.master_id = '1' AND ht.category_id = hcm.category_id AND ht.sub_category_id = hscm.sub_category_id ORDER BY ticket_id DESC LIMIT 0,10 I have thought of options such as using temporary tables to just grab the last 10 tickets and then do an IN query, however I need to display totals, so that would require me to run the query again. My questions are: 1) Is there any point to having an index on a column with so few unique values? 2) Would it make more sense to have multiple master tables for each helpdesk? Such as: helpdesk_tickets_1 helpdesk_tickets_2 helpdesk_tickets_3 etc. and then using a session value to query the table? 3) Any other tips or advice? (I notice my query time doubles from 100k rows to 150k rows) Thanks for any help... Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
