Hello, my code , mysql tables needs changing...

[menntarra_34]

I wrote a search script but i think it needs to be changed cause it is not very good, and my server have VERY high load, i explain it, ask if you don't understand :

 

I post the problematic part, any suggestion is welcome, on how to change the structure of mysql tables, or php code...

 

###Tables are like this:
###tables with their coloumns:

###big_table(id,value1,value2,value3 etc...)
###search_words(id,words)
###connection(search_words_id,big_table_id)

###Our goal is to get results from big_table based on words from search_words table!

$keywords are like this: "plane car football"

$keywords = explode(" ", $keyword);

foreach ($keywords as $value) {

SELECT big_table_id FROM search_words AS t LEFT JOIN connection AS tv ON tv.search_words_id = t.id WHERE words = '". $value ."' AND big_table_id IS NOT NULL

                $result = mysql_query($query);
                while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                  $big_id_results[$value][] = $row[0];
                }
                    
}

if (count($big_id_results) == 1) {
              $all_ids = array_shift($big_id_results);
            }
            else {
              $all_ids = call_user_func_array('array_intersect', $big_id_results);
            }
            
###And lastly we get results  from big table, with the the ids:

$query = "SELECT * FROM big_table WHERE active=1 AND id IN (".implode(", ",$all_ids)."

 

So basicly this is my search algorithm, if you can think of anything better , don't hesitate to tell me...

My long queries are because of this last query:

$query = "SELECT * FROM big_table WHERE active=1 AND id IN (".implode(", ",$all_ids).") LIMIT 0 , 30";

 

Because sometimes "$all_ids" have like 250.000 ids separated by comma...

[fenway]

Well, why the LEFT JOIN -- you're not checking IS NULL anywhere.

 

Also, if you're using LIMIT 30, why give the server 250K IDs?

[menntarra_34]

Thanks for your answer, i made som modification, but because it is a search engine, if there are criterias, then i NEED THE ORDER BY mysql function a lot of times, and i need to order that 250.000 ids, cause i want to get ids from all of it. I mean if i just include for example 30 ids and order them, that is not okay, cause then the data won't be real becase the order by will only order that 30 result set...

 

What do you mean why use left join, what do you advise instead, that would mean performance increase?

[fenway]

If you switch to INNER JOIN, it might be much faster, depending on the number of rows in each table.

[menntarra_34]

Thank you! I did a lot of changes, and load gone down , but still a little high, and as i looked at the slow query log, i think that there are a lot of inserts and updates of my tables, would that be a good idea to make another separate db just for UPDATES/INSERTS/REPLACES , and use it for that, and the other db, just for READS, and i would sync them once or twice a day, maybe that is a good idea? If yes, do you know any good sync methods of 2 db-s?

[fenway]

No, don't create another db.

 

You didn't find inserts in the slow_query_log.