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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
