Hi Daniel,
Here's some more data which might help. This query does not appear to be
using the composite index although there is one on the master_id and
ticket_id. The below query takes about 3 seconds. Anyway I think I will
still move to seperate tables for each master id. Thanks for the reply.
EXPLAIN SELECT *
FROM helpdesk_tickets ht
WHERE ht.master_id = '1'
ORDER BY ticket_id DESC
LIMIT 0,10
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra,
1,SIMPLE,ht,range,master_id,master_id,4,,168439,Using where; Using
filesort,
CREATE TABLE `helpdesk_tickets` (
`ticket_id` int(5) NOT NULL auto_increment,
`running_id` int(5) NOT NULL default '0',
`master_id` int(5) NOT NULL default '0',
`category_id` int(5) NOT NULL default '0',
`sub_category_id` int(5) NOT NULL default '0',
`priority_id` int(5) NOT NULL default '0',
`location_id` int(5) NOT NULL default '0',
`status_id` int(5) NOT NULL default '0',
`user_logging_id` int(5) NOT NULL default '0',
`user_problem_id` int(5) default '0',
`other_user_problem` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`description` text NOT NULL,
`submit_date` datetime default NULL,
`submit_month` tinyint(2) NOT NULL default '0',
`complete_date` datetime default NULL,
`feedback_id` int(5) NOT NULL default '0',
`review_id` int(5) NOT NULL default '0',
`feedback_comments` text NOT NULL,
`review_comments` text NOT NULL,
`significance_id` int(5) NOT NULL default '0',
`time_spent` int(5) NOT NULL default '0',
`requires_ams_update` enum('y','n') NOT NULL default 'n',
`duplicate_ticket` enum('y','n') NOT NULL default 'n',
`friendly_id` int(5) NOT NULL default '0',
`due_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ticket_id`),
UNIQUE KEY `problem_id` (`ticket_id`),
KEY `priority_id` (`priority_id`),
KEY `category_id` (`category_id`),
KEY `sub_category_id` (`sub_category_id`),
KEY `location_id` (`location_id`),
KEY `user_logging_id` (`user_logging_id`),
KEY `user_problem_id` (`user_problem_id`),
KEY `status_id` (`status_id`),
KEY `submit_date` (`submit_date`),
KEY `running_id` (`running_id`),
KEY `submit_month` (`submit_month`),
KEY `master_id` (`master_id`),
KEY `comp_ticket_master` (`ticket_id`,`master_id`),
FOREIGN KEY (`user_logging_id`) REFERENCES `intranet_user_login`
(`user_login_id`),
FOREIGN KEY (`sub_category_id`) REFERENCES
`helpdesk_sub_category_master` (`sub_category_id`),
FOREIGN KEY (`category_id`) REFERENCES `helpdesk_category_master`
(`category_id`)
) TYPE=InnoDB CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free:
97280 kB'
Daniel wrote:
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]
