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

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Building pagination doesn't seem to be possible! There has to be a solution to this!


    Hi;

    I can't find the answer to this, I really appreciate if you guys give me your input on it.

    It is about paginations. It seems to be very popular problem with php and mysql. I have no problems making paginations for small databases but sometimes when database gets bigger (+5 million) and the search results return many rows, calcuateing number of results get very slow. Let me show an example
    PHP Code:
    public function search_products($data$record_start=0,$number_of_records=10)
        {
            
            
    $query $this->db->select('id, code, class, price')->from('products_table')->get_where(''$data$number_of_records$record_start0);

            return 
    $query->result_array(); 
        } 
    This is the first query that produces
    Code:
     SELECT `id`, `code`, `class`, `price`
    FROM (`products_table`)
    WHERE `class` =  '4'
    AND `category` =  'M'
    LIMIT 100
    and average takes 0.0189 seconds nice and easy.

    And here is the problem:

    this is the second query without limit, so I can specify the total number of rows (in this case it is CodeIgniter's total_rows ).
    PHP Code:
    public function search_products_number_of_results($data)
        {
            
            
    $query $this->db->select('COUNT(*) AS C')->from('products_table')->get_where(''$dataNULL0);
            return 
    $query->result_array();
        } 
    Which produces
    Code:
     SELECT COUNT(*) AS C
    FROM (`products_table`)
    WHERE `class` =  '4'
    AND `category` =  'M'
    It never takes lesst than 2.7 seconds (for approximately 25000 row count). How can I calculate total number of records without having to deal with slow query.
    Code:
     CREATE TABLE `products_table`
      (
         `id`                INT(11) NOT NULL auto_increment,
         `code`              VARCHAR(4) NOT NULL,
         `class`             INT(11) NOT NULL,
         `category`          VARCHAR(5) NOT NULL,
         `price`             DECIMAL(6, 2) NOT NULL,
         `production_date`   DATE NOT NULL,
         `quick_description` VARCHAR(255) NOT NULL,
         `description`       TEXT NOT NULL,
         `status`            INT(11) NOT NULL,
         PRIMARY KEY (`id`),
         KEY `production_date` (`production_date`),
         KEY `index_price` (`price`),
         KEY `class_index` (`class`),
         KEY `category_index` (`category`),
         KEY `status_index` (`status`)
      )
    engine=innodb
    auto_increment=6222518
    DEFAULT charset=latin1
    Please note I do not want the estimate results, it is not reliable.
    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)
    3 million difference!

    Thank you Devshed
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,959
    Rep Power
    9397
    Sometimes there just isn't a good answer. This is one of those times. Short of upgrading hardware your best bet is probably to cache the counts. An occasional 2.7s but most of the time it's a cache hit.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,967
    Rep Power
    374
    you might get added headaches but how about storing the total rows in a session (for user) or in a config item (for everyone).. headache will come because depending on how much you update this table, you will need to re-run the query every now and then to update the session/config-item.
  6. #4
  7. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,547
    Rep Power
    2337
    Make an index on (class, category) and see if that improves the performance of that query. It will have slightly negative effect on insert performance, as indexes are wont to do.
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  8. #5
  9. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    My limited knowledge

    - tweak your my.ini settings to get all of your database into RAM (my mysql performance seeking thread)

    - only select one column in the count (eg count(`id`) as C)
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  10. #6
  11. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by Northie
    - tweak your my.ini settings to get all of your database into RAM (my mysql performance seeking thread)
    This is intresting, just checking it out.
    Originally Posted by Northie
    - only select one column in the count (eg count(`id`) as C)
    Not sure about that,,, SimplySQL By Rudy Limeback page 146
    It turns out that COUNT(*) is extremely fast in comparison to the COUNT aggregate function used on a particular column. The reason has to do with the fact that the database system doesn’t have to examine any values looking for NULLs when it calculates COUNT(*).
    Always use COUNT(*) if you are interested in the number of rows, not the number of values.
    Thanks
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by requinix
    Sometimes there just isn't a good answer. This is one of those times. Short of upgrading hardware your best bet is probably to cache the counts. An occasional 2.7s but most of the time it's a cache hit.
    There are 6 columns for search criteria. status, code, class, p_date, category and price. Each of them could be different everytime the search happens so there are unlimited situations.

    How am I supposed to cache each scenario? Please explain thanks.

    An example:
    Code:
     SELECT `products_table`.`id`,
           `code`,
           `class`,
           `category`,
           `status`,
           `price`,
           `production_date`,
           `products_status`.`title` AS STATUS
    FROM   products_table
           JOIN `products_status`
             ON `products_status`.`id` = `products_table`.`status`
    WHERE  `class` = 7
           AND `category` = 'Z'
           AND price BETWEEN 100 AND 149
           AND status = 4
           AND code = 'c'
    LIMIT  200, 100

IMN logo majestic logo threadwatch logo seochat tools logo