[Solved] Linked Tables and SQL SELECT Queries

EverSQL Database Performance Knowledge Base

Linked Tables and SQL SELECT Queries

Database type:

I have a few linked tables in my custom forum: categories, sub_categories and posts

basically, I have people able to add up to three categories and five sub-categories when they make a new post.

I also enable people to 'listen' to certain categories and sub-categories and have them in an easy to access bar at the side of the page.

My tables are set up thus (only showing relavent fields for ease):

posts:

id              INT
category_id     VARCHAR(12)
sub_category_id VARCHAR(35)

categories:

id      INT
name    VARCHAR(20)

sub_categories:

id      INT
name    VARCHAR(20)

in my posts table, I store the set categories and sub-categories by their ID in the following format:

category_id     [2][4][8]
sub_category_id [1][2][3][4][5]

thus enabling me to execute the following query in PHP and get the post based on category and sub-category:

SELECT * FROM posts WHERE category_id LIKE '%[{$id}]%' AND sub_category_id LIKE '%[{$id2}]%'

the problem I have is selecting the sub_categories for the access bar that people 'listen' to...

$sql = "SELECT title, id FROM categories";
$query = mysql_query($sql);
$list = array();
while ($row = mysql_fetch_assoc($query)){
    $list[$row['title']] = array();
    $sql = "SELECT sub_categories.title FROM sub_categories, posts WHERE (category_id LIKE '%[{$row['id']}]%') AND (????) LIMIT 0,100";
    $query = mysql_query($sql);
    while($result = mysql_fetch_assoc($query)){
        $list[$row['title']][] = $result['title'];
    }
}
print_r($list);

Obviously you can see where I am stuck (????), but before I explain what I am trying to do, I'll explain what the output I am looking for is.

when I print the $list array, I want it to print a multi-dimensional array featuring the categories as the first key, with their values being an array of sub-categories that have been tagged in the main category.

The problem I have is that in my sub_category_id field on the post table, remember the values are stored in the format [1][2][3] and I need to check the value against the subcategory field id.

I have tried the following:

"SELECT sub_categories.title FROM sub_categories, posts WHERE (category_id LIKE '%[{$row['id']}]%') AND (sub_category_id LIKE '%[sub_categories.id]%') LIMIT 0,100"

But it didn't work. I don't know whether there is an error in my query or whether it even SHOULD work, but I would be grateful if anyone could tell me how to do it or where I am going wrong in my code!

NB. I am trying to find which sub_categories appear in which categories based on people tagging them in a post together.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid LIKE Searches With Leading Wildcard (query line: 6): The database will not use an index when using like searches with a leading wildcard (e.g. '%[{$id}]%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  2. Avoid LIKE Searches With Leading Wildcard (query line: 7): The database will not use an index when using like searches with a leading wildcard (e.g. '%[{$id2}]%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  3. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        * 
    FROM
        posts 
    WHERE
        posts.category_id LIKE '%[{$id}]%' 
        AND posts.sub_category_id LIKE '%[{$id2}]%'

Related Articles



* original question posted on StackOverflow here.