MYSQL PHP querying already queried data ( Filtering already filtered data)

I have a complex query that returns more than 30,000 records . We are showing user at a time 100 records and user has to click on next to get next 100 records .

Here the problem is we are running the complex query against the database again and adding LIMIT 100 , 200 to fetch the next 100 records .

In addition to this complexity we have other filters at client end which has to be performed on the 30,000 records .There are around one l00,000 users in the database . So for very activity we are running that complex query again and again .

Is there any way to keep the 30,000 records and run the operations on it ? I checked Temporary MYSQL tables . But I don't think we can use them for this scenario . To store the data in physical table and query against it , It is really going to be a big mess if 1000 users tries access this future at a time and to handle it programatically ( insert and delete operations )

UPDATED TO GIVE MORE DETAILS

-> Here is the exact scenario , Cars company wants to see all the cars in the given distance , for example 100 Miles radius from the given latitude and longitude with the car and user details . After doing this operation in the combox displays the car models and below that data table with all the information . Again on top of each column there is a text field for searching on that column ( For example on customer name , entering two letters starts filtering the data) . 1) Getting the cities with in 100 miles 2) querying the database ( combining 5 tables here) 3) Get the cars models list from the above query and displaying it in the list box 4) displaying the 100 records to the user 5) Filtering based on the copbox box list and the text fields on each column

SO simple :) -->

Thanks for your help

Regards

Kiran

This question and answers originated from www.stackoverflow.com
Question by (4/28/2011 3:37:26 PM)

Answer

My company's application deals with more than 2 million records and numerous joins, and we handle this exact problem very gracefully with jQuery DataTables. Yes, that's front-end technology....but the interaction between the user, datatables, and our queries is what makes it a really elegant solution. Aside from indexing the tables, we don't do any views or stored procedures at all, and it works suitably zippy.

DataTables is a grid--a table with extra features including sorting, paging, filtering, etc. It grabs data via Ajax, X at a time, and adds additional elements to the query to make the extras happen. Where it really shines is when you use a feature called "pipelining" where it grabs data ahead of and behind what you're displaying, cutting down on the volume of queries while giving the user a much faster viewing experience. It's a built in feature and is very easy to deploy.

Here's the tut on pipelining: http://datatables.net/examples/server_side/pipeline.html

And here's a sample of the query that I use:

public static function getQueue( $q=false) {
            $connect = new db_connection();

            $query  = ' SELECT something
                                    FROM table
                        WHERE 1 ';

            if ($q['id']) {
                $query .= " AND id = ".$q['id']." ";
            }

            if ($q['sortOrder']) {
                $query .= "ORDER BY " . $q['sortField']. " " .$q['sortOrder']. " ";
            }

            if ($q['limit']) {
                $query .= ' LIMIT '.$q['limit'];
            }

            if ($q['offset']) {
                $query .= ' OFFSET '.$q['offset'];
            }

            $result = $connect->query($query);
            $queue_array = $connect->fetchAll($result);

            return $queue_array;

        }

If you've never used Jquery Ajax before, it's looking for a result in a string. So, I have a page that calls the function below with the query above to echo out the result. I call it my ajaxDispatcher:

public static function showQueue($info) {

            $q['limit'] = $info['iDisplayLength'];
            $q['offset'] = $info['iDisplayStart'];

            //Ordering
            if ( isset( $info['iSortCol_0'] ) )  {
              for ( $i=0 ; $i < $info['iSortingCols']; $i++ ) {
                  $q['sortField'] .= self::queueHeaders($info['iSortCol_'.$i]);
                  $q['sortOrder'] .= $info['sSortDir_'.$i];
              }
            }

            $callqueue = self::getQueue($q);                    //Gets results with dependencies
            $prospect_count = self::getCount();                 //Gets total record count

            $string = '{"sEcho": '.intval($_REQUEST['sEcho']).', "iTotalRecords": '.intval($prospect_count).', "iTotalDisplayRecords": '.(int)$prospect_count.', "aaData":[';


            foreach ($callqueue as $c ) {
                $locked = ($c['locked'] == 1)?'<input type="checkbox" checked disabled>':'<input type="checkbox" disabled>';
                $scheduled_date = ($c['callback_date_time'])?date("M d Y g:ia", strtotime($c['callback_date_time'])):'';

                if ($count != 0)  $midstring .= "," ;
                $midstring .= '["'.$c['priority'].'", "'.addslashes($locked).'", "'.$scheduled_date.'", "'.$c['company_name'].'", "'.$c['marcom_name'].'", "'.$c['marcom_description'].'"]';
                $count++;
            }

            $endstring .= ']}';

            $finalstring = $string.$midstring.$endstring;

            return $finalstring;

     }

This tool was a gamechanger for our company when I finally got it all figured out. We went from a (poorly designed) manual paging system to ajax calls and interactivity that made all the difference in the user experience. In a way, you're "tricking" the user by the spacing of the query and the interaction....but it just works.

Answer by
Find More Answers
Related Topics  php  mysql  query  filter
Related Questions