#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,585
    Rep Power
    171

    How to deal with slow query MVC


    Hi;

    I have a simple situaition that I need to improve the performance! I am surprised I can't find the answer! I appreciate help:

    Scenario 1 takes 5 seconds, type index rows 5522015:
    Model
    PHP Code:
    public function count_products()
        {
            
    $query $this->db->query("SELECT 1 FROM products_table order by id");
            return 
    $query->num_rows();
        } 
    Controller
    PHP Code:
    public function __construct()
       {
            
    parent::__construct();
            
    $this->load->model('model_products');
            echo 
    $this->number_of_products $this->model_products->count_products();
        } 
    Scenario 2 uses index rows 5522015 takes 17 seconds
    Model
    PHP Code:
    public function count_products()
        {
            
    $this->db->from('products_table');
            return 
    $this->db->count_all_results();    
        } 
    Controller
    PHP Code:
    public function __construct()
       {
            
    parent::__construct();
            
    $this->load->model('model_products');
            
    $this->number_of_products $this->model_products->count_products();
        } 
    Code:
    CREATE TABLE `products_table` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `code` varchar(4) NOT NULL,
     `class` varchar(11) NOT NULL,
     `category` varchar(5) NOT NULL,
     `price` decimal(6,2) NOT NULL,
     `production_date` date NOT NULL,
     PRIMARY KEY (`id`),
     KEY `production_date` (`production_date`),
     KEY `index_price` (`price`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5521512 DEFAULT CHARSET=latin1
    Thank you
  2. #2
  3. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,760
    Rep Power
    9397
    Or how about
    Code:
    SELECT COUNT(*) FROM products_table
    instead? Then fetch the single column from the result (rather than use num_rows).
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,585
    Rep Power
    171
    Originally Posted by requinix
    Or how about
    Code:
    SELECT COUNT(*) FROM products_table
    instead? Then fetch the single column from the result (rather than use num_rows).
    That is what the model in scenario 2 produces. It is also very slow.

    I am very surprised cannot fnd an answer to something this simple. Also discussed it in MySql forum but Rudy didn't have a better solution either!

    What would be your decision if you are in this situation requinix?

    Thanks
  6. #4
  7. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,760
    Rep Power
    9397
    Originally Posted by zxcvbnm
    Also discussed it in MySql forum but Rudy didn't have a better solution either!
    Yet. You only just now asked him what he would do.

    You said the opposite in the other thread:
    Originally Posted by zxcvbnm
    This takes 5 seconds, type index rows 5522015:
    Code:
    SELECT Count(*)
    FROM   producst_table;
    This takes 17 seconds:
    Code:
    SELECT 1
    FROM   producst_table;
    Thank you
    That I find much more believable.

    Like r937 said if you want an instant response then switch to MyISAM, but you'll lose out on some features that InnoDB offers. If you need a rough figure you can instead do a SHOW TABLE STATUS.
    Programmatically you have options like caching.
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,585
    Rep Power
    171
    Originally Posted by requinix
    Like r937 said if you want an instant response then switch to MyISAM, but you'll lose out on some features that InnoDB offers. If you need a rough figure you can instead do a SHOW TABLE STATUS.
    Programmatically you have options like caching.
    Thanks it's all good.
    PHP Code:
    public function __construct()
       {
            
    parent::__construct();
            
    $this->load->library('parser');
            
    $this->load->model('model_products');
            
    $this->number_of_products $this->model_products->count_products();
        } 
    PHP Code:
    public function count_products()
        {
            
            
    $query $this->db->query("SHOW TABLE STATUS LIKE 'products_table'");
            
    $results $query->result();
            return 
    $results[0]->Rows;    
        } 
  10. #6
  11. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,585
    Rep Power
    171
    Originally Posted by requinix
    If you need a rough figure you can instead do a SHOW TABLE STATUS.
    PHP Code:
    public function count_products()
        {
            
            
    $query $this->db->query("SHOW TABLE STATUS LIKE 'products_table'");
            
    $results $query->result();
            return 
    $results[0]->Rows;    
        } 
    That estimate is ridiculous! This is the results for the same query:
    Showing rows 0 - 29 (~7,830,9091 total, Query took 0.0006 sec)
    howing rows 0 - 29 (~5,006,1911 total, Query took 0.0006 sec)
    Showing rows 0 - 29 (~4,052,7541 total, Query took 0.0006 sec)
  12. #7
  13. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,760
    Rep Power
    9397

IMN logo majestic logo threadwatch logo seochat tools logo