Categories

Bluetrait
        Bluetrait
            Coding
            Geek
            General
            Coding
                PHP
                Bluetrait
                WordPress
                    Plugins
                PHP
                Bluetrait (Program)
            Geek
                Juniper
                Cisco
                IBM N2200 8363
                PCs
                Spam
                IPv6
                Apple
                NetScreen
                Internet
            General
                Uni

Tue, 21 Jun 2011 11:33 AM

IPManager 2.2 tickets sql improvements.

Michael Dale

Primary tickets query in IPManager, working very nicely (110,000 tickets in a slow VM).

SQL result

Host: localhost
Database: ipm_test
Generation Time: Jun 21, 2011 at 01:10 AM
Generated by: phpMyAdmin 3.2.0.1 / MySQL 5.1.36-community-log
SQL query: EXPLAIN SELECT tickets.* , c.id AS `source_client_id`, c.client_name AS `source_client_name`, c.email AS `source_client_email`, c2.client_name AS `assigned_user_name`, c2.email AS `assigned_user_email`, comp.name AS `source_company_name`, p.name AS `priority_name`, t.name AS `ticket_state_name` FROM tickets LEFT JOIN clients c ON c.id = tickets.source_client_id LEFT JOIN clients c2 ON c2.id = tickets.assigned_user_id LEFT JOIN priorities p ON p.id = tickets.priority_id LEFT JOIN ticket_state t ON t.id = tickets.ticket_state_id LEFT JOIN companies comp ON comp.id = tickets.source_company_id WHERE 1 = 1 AND tickets.ticket_state_id = 1 ORDER BY last_modified DESC LIMIT 50 OFFSET 0;
Rows: 6

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tickets index ticket_state_id last_modified 8 NULL 50 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 ipm_test.tickets.source_client_id 1  
1 SIMPLE c2 eq_ref PRIMARY PRIMARY 4 ipm_test.tickets.assigned_user_id 1  
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 ipm_test.tickets.priority_id 1  
1 SIMPLE t const PRIMARY PRIMARY 4 const 1  
1 SIMPLE comp eq_ref PRIMARY PRIMARY 4 ipm_test.tickets.source_company_id 1  

PHP

Comments

Comments?

HTML allowed: <a href="" title="" rel=""></a> <b></b> <blockquote cite=""></blockquote> <em></em> <i></i> <strike></strike> <strong></strong> <li></li> <ol></ol> <ul></ul>
ie: <b>bold</b>

Your comment may need to be reviewed before it is published.

Message

Name

Email (not shown)

WWW (optional)

Allow contact form email

Remember details