Categories

Bluetrait
        Bluetrait
                Bluetrait
                    Coding
                    Geek
                    General
                    Videos
                    Solar
                    Coding
                    Geek
                    General
                    Coding
                        PHP
                        Bluetrait
                        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