need to split up a large table into smaller ones

[sqlbeginner]

Hi,

Beginner coder here...

Wondering if someone can point me in the right direction for more reading...unless you have the time to type it here :)

I have a table that has grown to over 12,000KB and my server's php admin area now limits file upload to a bit over 8,000 KB. I have have 6 main categories of info in this large table so I'm thinking of splitting the table into 6 smaller ones. I think it will be easier to manage.

The first question I have is what's the first step to go about making the necessary changes. Splitting the tables is easy enough. The issue is on the web site, when someone does a search, being able to search all of the tables at once. Would this be a "join" type of code that needs to be written? Or is there a way to create a table that can read the contents of the 6 smaller ones?

Looking for the first step...

TIA,

Jim

[techietim]

Yeah, try looking at the Tizag Mysql Join Tutorial (http://www.tizag.com/mysqlTutorial/mysqljoins.php). Keep in mind that you'll have to fix up your code to meet the new table structure.

[Stryker250]

well if you are using mysql here is a query you could try



$query = "SELECT * FROM tbl1, tbl2, tbl3, tbl4, tbl5, tbl6 WHERE tbl1.id = '$id' OR tbl2.id = '$id' OR tbl3.id = '$id' OR tbl4.id = '$id' OR tbl5.id = '$id' OR tbl6.id = '$id'";
$query = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['id']."<br />";
}

[sqlbeginner]

Thanks for the quick reply. In the back of my mind it flashed "searchhtbl1, tbl2..." but I wasn't sure.

So for my situation it would look something like this?

$search= mysql_query"SELECT attrib1, attrib2, attrib3 FROM tbl1, tbl2, tbl3, tbl4, tbl5, tbl6 WHERE tbl1.attrib1 = '$attrib1' OR tbl2.attrib1 = '$attrib1' OR tbl3.attrib1 = '$attrib1' OR tbl4.attrib1 = '$attrib1' OR tbl5.attrib1 = '$attrib1' OR tbl6.attrib1 = '$attrib1'";
....

Results of this search query would be to look for a match to attrib1 within the 6 tables and pull the info in the 3 attributed fields for display purposes.

[Stryker250]

yes that should work...but remember that mysql_query has (brackets) which need to be used....otherwise you will get a php error or a mysql error.

so it would be like this



$search= mysql_query("SELECT attrib1, attrib2, attrib3 FROM tbl1, tbl2, tbl3, tbl4, tbl5, tbl6 WHERE tbl1.attrib1 = '$attrib1' OR tbl2.attrib1 = '$attrib1' OR tbl3.attrib1 = '$attrib1' OR tbl4.attrib1 = '$attrib1' OR tbl5.attrib1 = '$attrib1' OR tbl6.attrib1 = '$attrib1'");

[sqlbeginner]

umm...yeah...I wudda figured that out :D Tks. I'll probably be trying this over the weekend so I'll let you know how it plays out.

Since I add my new data via a browser interface I'll have to add a new field to the form to choose which table to insert data into. But....I'm thinking that since the current large table is set with auto_increment for the unique indentifier of each row (row_id), this could cause problems if I was using multiple tables. Almost immediately there would be conflict of duplicate entries.

I wonder what I would do there? If I disabled auto_increment and added yet another new field to the data entry form for row id, I would have to manually track the next row_id number to input.

Something else too, thinking about it now, I suppose if I wanted to add even more tables later on its just a matter of adding another "tbl#". That being the case I could change the ..tbl1, tbl2... to a string and place the string value in the primary file that gets loaded when the page is called.

Any comments?

Thanks,

[sqlbeginner]

I get the following error msg
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

I split the large table into 8 smaller ones, based on types of data.
This is the query where the message occurs. Its a call to count all entries and display a total sum.


$mysql_db_tbl1 = "links1";
$mysql_db_tbl2 = "links2";
$mysql_db_tbl3 = "links3";
$mysql_db_tbl4 = "links4";
$mysql_db_tbl5 = "links5";
$mysql_db_tbl6 = "links6";
$mysql_db_tbl7 = "links7";
$mysql_db_tbl8 = "links8";

$mysql_db_linkstables = "links1, links2, links2, links4, links5, links6, links7, links8";


$rime11 = mysql_query("SELECT COUNT(*) from $mysql_db_linkstables WHERE
$mysql_db_tbl1.link_val = '$link_val' OR
$mysql_db_tbl2.link_val = '$link_val' OR
$mysql_db_tbl3.link_val = '$link_val' OR
$mysql_db_tbl4.link_val = '$link_val' OR
$mysql_db_tbl5.link_val = '$link_val' OR
$mysql_db_tbl6.link_val = '$link_val' OR
$mysql_db_tbl7.link_val = '$link_val' OR
$mysql_db_tbl8.link_val = '$link_val' LIKE 'yes' AND
$mysql_db_tbl2.cat_id = '$id' OR
$mysql_db_tbl3.cat_id = '$id' OR
$mysql_db_tbl4.cat_id = '$id' OR
$mysql_db_tbl5.cat_id = '$id' OR
$mysql_db_tbl6.cat_id = '$id' OR
$mysql_db_tbl7.cat_id = '$id' OR
$mysql_db_tbl8.cat_id = '$id'
") or die(mysql_error());
$totalnum = mysql_result($rime11, 0, 0);


The original query looks like this and works fine:
$rime11 = mysql_query("SELECT COUNT(*) from links WHERE link_val LIKE 'yes' AND cat_id='$id'") or die(mysql_error());
$totalnum = mysql_result($rime11, 0, 0);


Is there someone who's willing to help me through this? Thanks.

[DragoNero]

something i usually find with MySQL strings is that they dont like PHP variables so....


$query = "Select COUNT(*) from " . $mysql_db_linkstables . " WHERE
" . $mysql_db_tbl1.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl2.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl3.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl4.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl5.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl6.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl7.link_val . " = '" . $link_val . "' OR
" . $mysql_db_tbl8.link_val . " = '" . $link_val . "' LIKE 'yes' AND
" . $mysql_db_tbl2.cat_id . " = '" . $id . "' OR
" . $mysql_db_tbl3.cat_id . " = '" . $id . "' OR
" . $mysql_db_tbl4.cat_id . " = '" . $id . "' OR
" . $mysql_db_tbl5.cat_id . " = '" . $id . "' OR
" . $mysql_db_tbl6.cat_id . " = '" . $id . "' OR
" . $mysql_db_tbl7.cat_id . " = '" . $id . "' OR
" . $mysql_db_tbl8.cat_id . " = '" . $id . "' ";

$rime11 = mysql_query($query) or die(mysql_error());


although i dont think that would cause the error your getting...

the error says (i think...) that you need to specify how many table joins ur using

SET SQL_MAX_JOIN_SIZE=#

or state that you using a big join select

SET SQL_BIG_SELECTS=1

[sqlbeginner]

I found a much cleaner solution to this issue. Using the MERGE table in phpMyAdmin.