在搜索中使用mysql和php处理大量数据

I'm making a car part system, to store all the parts inside mysql and then search for them.

Part adding goes like this: you select up to 280 parts and add all the car info, then all the parts are serialized and put into mysql along with all the car info in a single row.

(for this example I'll say that my current database has 1000 cars and all of those cars have 280 parts selected)

The problem is that when I have 1000 cars with each of them having 280 parts, php and mysql starts getting slow and takes a lot of time to load the data, because the number of parts is 1000*280=280 000.

I use foreach on all of the cars and then put each part into another array. The final array has 280 000 items and then I filter it by the selected parts in the search, so out of 28 000 parts it may have only have to print like 12 500 parts (if someone is searching for 50 different parts at the same time and 250 cars have that part).

Example database: http://pastebin.com/aXrpgeBP

$q=mysql_query("SELECT `id`,`brand`,`model`,`specification`,`year`,`fueltype`,`capacity`,`parts`,`parts_num` FROM `warehouse`");
    while($r=mysql_fetch_assoc($q)){
        $partai=unserialize($r['parts']);
        unset($r['parts']); //unsetting unserialized parts so the whole car parts won't be passed into the final parts-only array
        foreach($partai as $part){
        $r['part']=$parttree[$part]; //$parttree is an array with all the part names and $part is the part id - so this returns the part name by it's id.
        $r['part_id']=$part; // saves the part id for later filtering selected by the search
        $final[]=$r;
        }
    }

            $selectedparts=explode('|', substr($_GET['selected'], 0,strlen($_GET['selected'])-1)); //exploding selected part ids from data sent by jquery into an array

    foreach($final as $f){
        if(in_array($f['part_id'], $selectedparts)){
            $show[]=$f; //filtering only the parts that need to be shown
        }
    }

    echo json_encode($show);

This is the code I use to all the cars parts into arrays and the send it as json to the browser.

I'm not working on the pagination at the moment, but I'll be adding it later to show only 10 parts.

Could solution be to index all the parts into a different table once 24h(because new parts will be added daily) and then just stressing mysql more than php? Because php is doing all the hard work now.

Or using something like memcached to store the final unfiltered array once 24h and then just filter the parts that need to be shown with php?

These are the options I considered, but I know there must be a better way to solve this.

Your query should be something like:

<?php

$selectedparts=explode('|', substr($_GET['selected'], 0,strlen($_GET['selected'])-1)); //exploding selected part ids from data sent by jquery into an array
$where = ' id < 0 ';
foreach ($selectedparts AS $a){
$where .= " OR `parts` like '%".$a."%'";
}
$query = "SELECT * FROM `warehouse` WHERE ".$where." ORDER BY `id` ASC";//this is your query
//.... rest of your code

?>

Yes, you should definitely put more emphasis on MySQL. Don't serialize the parts for each car into a single row of a single column. That's terribly inefficient.

Instead, make yourself a parts table, with columns for the various data items that describe each part.

   part_id     an autoincrement item.
   car_id      which car is this a part of
   partnumber  the part's external part number (barcode number?)
   etc

Then, use JOIN operations.

Also, why don't you use a WHERE clause in your SELECT statement, to retrieve just the car you want?

Edit

If you're looking for a part, you definitely want a separate parts table. Then you can do a SQL search something like this.

  SELECT w.id, w.model, w.specification, w.year, w.fueltype,
         p.partnumber
    FROM warehouse w
    JOIN parts p ON (w.id = p.car_id)
   WHERE p.partnumber = 'whatever-part-number-you-want'

This will take milliseconds, even if you have 100K cars in your system, if you index it right.

Yes, look into has many relationships a car has many parts.

http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/

Then you can use an inner join to get the specified parts. You can do a where clause to match the specific partIds to filter out unwanted parts or cars.