On 5/28/05, Terence wrote:
Master ID is used to distinguish multiple helpdesks. In this table there
are 100k records, but only 10 distinct master_id's.
ticket_id master_id
1 1
2 1
3 2
4 2
5 3
... ...
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
With proper foreign keys all tickets are guaranteed to match
categories and subcategories. That means you can push down the LIMIT
to just the helpdesk_tickets table:
SELECT *
FROM (
SELECT *
FROM helpdesk_tickets
WHERE master_id = 1
ORDER BY ticket_id DESC
LIMIT 0,10)
NATURAL JOIN helpdesk_category_master
NATURAL JOIN helpdesk_sub_category_master
With an index on (master_id, ticket_id) this should (nearly) constant-time.
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.
At which point does running the query again become faster then your
current method?
Jochem
6 messages in com.mysql.lists.mysqlRe: performance on single column inde...