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]
