Welcome to WebmasterWorld Guest from 50.19.34.255

Forum Moderators: open

Should I JOIN tables or merge them into one physical table?

     
2:27 am on Aug 28, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


I currently have a table with about 300,000 rows and 22 columns. One those columns is "category".

If "category" is one thing, then 6 of those rows are used; otherwise, they're NULL. About 65,000 rows match this category.

If "category" is another thing, then 5 other columns are used (not the same as the previous 6); otherwise, they're NULL. About 5,200 rows match this category.

If "category" is anything besides those 2 things, then 11 of those 22 columns are NULL. So about 230,000 of those 300,000 rows have 11 columns that are NULL.

I'm rebuilding the section of my site that uses this table, so now I'm wondering, which is better:

1. Leaving the table as-is, with so many rows that have so many NULL columns; or

2. Splitting this in to 3 tables, moving the 6 columns to one table, and the 5 columns to another table.

And if it's better to have the 3 tables, should I:

A. Do a LEFT JOIN on the tables with every query, even though most of them won't join anything; or

B. Do an initial query where I learn whether "category" matches something that requires one of the other two tables, and if so do a second query?

My current code, in PHP:
$query = sprintf("SELECT
id,
category,
subcat,
col4,
col5,
col6,
...
col22
FROM tableA
WHERE id = '%s'
LIMIT 1",
mysqli_real_escape_string($dbh, $_GET['id']));


If I do a LEFT JOIN with every query:
$query = sprintf("SELECT
id,
category,
subcat,
col4,
col5,
col6,
...
col22
FROM tableA
LEFT JOIN tableB
ON tableA.id = tableB.id
LEFT JOIN tableC
ON tableA.id = tableC.id
WHERE id = '%s' LIMIT 1",
mysqli_real_escape_string($dbh, $_GET['id']));


Or multiple queries:
$query = sprintf("SELECT
id,
category,
subcat,
col4,
col5,
col6,
...
col11
FROM tableA
WHERE id = '%s'
LIMIT 1",
mysqli_real_escape_string($dbh, $_GET['id']));

$sth_data = mysqli_query($dbh, $query);

while (list($id, $category, $junk) = mysql_fetch_row($sth_data)) {
$query2 = false;

if ($category == 'example') {
$query2 = "SELECT * FROM tableB WHERE id = $id";
$sth_moredata = mysqli_query($dbh, $query2);
list($col12, $col13,..., $col17) = mysql_fetch_row($sth_moredata)
}

elseif ($category = 'something_else') {
$query2 = "SELECT * FROM tableC WHERE id = $id";
$sth_moredata = mysqli_query($dbh, $query2);
list($col18, $col19,..., $col22) = mysql_fetch_row($sth_moredata)
}

...
}

Please excuse any typos, I just typed most of that up for this post.

Which do you guys think would be the better solution? Or, how would I determine which would be the better solution? The top priority is to make the query run (and the page load) as fast as possible.
10:15 am on Oct 15, 2017 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member piatkow is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 5, 2006
posts:3427
votes: 50


Sounds like the world has gone backwards since I was a DBA. Back at the turn of the century anything other than a fully normalised design would suggest that you were simply using the wrong software.
3:21 pm on Oct 18, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2610
votes: 0


Is category stored as text and do you search the DB against the category column often?