Optimising SQL Queries

Question

I'm developing a content management system at the moment, and I wanted to hear your thoughts on the following:

I have one table, page. Let's assume it looks like this

ID | Title | Content
1  | Test  | This is a test

As well as this, I have a page_option table (so I can store options relating to the page, but I don't want to have a finite list of options - modules could add their own options to a page if required.)

The page_option table could look like this:

page_id | option_key  | option_value
1       | background  | red
1       | module1_key | chicken

Now to retrieve a page object, I do the following using the Active Record class (this was pseudo coded for this question):

function get_by_id($page_id) {

 $this->db->where('id', $page_id);
 $page_object = $this->db->get('page');

 if($page_object->num_rows() > 0) {

   $page = $page_object->row();
   $this->db->where('page_id', $page_id);
   $options_object = $this->db->get('option');

   if($options_object->num_rows() > 0) {
     $page->options = $options_object->result();
   }

   return $page;

 }

 return $page_object->row();

}

What I want to know, is there a way to do this in one query, so that the option keys become virtual columns in my select, so I'd get:

ID | Title | Content        | background | module1_key
1  | Test  | This is a test | red        | chicken

In my results, rather than doing a seperate query for every row. What if there were 10,000? Etc.

Many thanks in advance!

You could do that, however, you will have to define in the query all the columns you want. You can not expect the query to dynamically set the amount of columns based on your options — Mosty Mostacho, Oct 22 '13 at 18:09
Ok, I was wondering if there was a way to do that - would there be a more optimal way of structuring my query then in that case? — Tom Hallam, Oct 22 '13 at 18:10
Well, you could apply the same logic as it is in this [question](http://stackoverflow.com/questions/9356374/mysql-multiple-rows-to-separate-fields/9356408#9356408) in your `page_option` table and then join to `page` to get its specific fields. — Mosty Mostacho, Oct 22 '13 at 18:15
Key value storage in MySQL is not advised.. you can't force background contains an valid color you need to enforce it with your application. An pivot will help in some cases but only if you have an small number off records in your page_option table because the GROUP BY will force an complete table scan or full index scan.. — Raymond Nijland, Oct 22 '13 at 18:20

score 1 · Accepted Answer · answered Oct 22 '13 at 20:10

Using the EAV (Entity-Attribute-Value) model you will always have to cope with these kind of issues. They're also not ver efficient due to the complexity of the queries (pivoting is required in most of them).

SELECT page_id,
  MAX(CASE WHEN option_key = 'background' THEN option_value END) background,
  MAX(CASE WHEN option_key = 'module1_key' THEN option_value END) module1_key,
  MAX(CASE WHEN option_key = 'module2_key' THEN option_value END) module2_key
FROM page_option
GROUP BY page_id

For example, given this table:

| PAGE_ID |  OPTION_KEY | OPTION_VALUE |
|---------|-------------|--------------|
|       1 |  background |          red |
|       1 | module1_key |      chicken |
|       2 | module1_key |         duck |
|       3 | module1_key |          cow |
|       4 |  background |         blue |
|       4 | module2_key |        alien |
|       4 | module1_key |      chicken |

You will the following output:

| PAGE_ID | BACKGROUND | MODULE1_KEY | MODULE2_KEY |
|---------|------------|-------------|-------------|
|       1 |        red |     chicken |      (null) |
|       2 |     (null) |        duck |      (null) |
|       3 |     (null) |         cow |      (null) |
|       4 |       blue |     chicken |       alien |

Fiddle here.

Then just join with the page table and that's it :) I've omitted that part in order to focus the query in the grouping itself.

Lajos Veres · Answer 2 · 2013-10-22T18:14:45.920

0

If you can add virtual fields with the activerecord class you can do something similar:

 $this->db->add_field("(select group_concat(concat(option_key,':',option_value)  SEPARATOR ' ') from page_option where page_id=$page_id group by page_id)");

It wont be optimal...

edited Oct 22 '13 at 18:14

answered Oct 22 '13 at 18:08

Lajos Veres

13,595
7
43
56

Thank you for your reply - but yeah I'd like the option of being able to call all relevant options rather than defining a finite set. – Tom Hallam Oct 22 '13 at 18:09
I modified a bit... In this way you don't have to list all options, but you have to parse the result. – Lajos Veres Oct 22 '13 at 18:15
1

I'd clarify that group_concat will not create different columns but rather one column with all the stuff together and then the OP will have to parse the column as a string separated by some value. Additionally, the gorup_concat has a limit on the amount items to concat (most likely much higher than the amount of options that a page can have). – Mosty Mostacho Oct 22 '13 at 18:20
Thanks for the attempt, I might give this a go and see how it fares in terms of performance - because really that's the only thing I'm worried about - if it works out similar to doing another query per row, then that's what I'll do! – Tom Hallam Oct 22 '13 at 18:21

score 0 · Answer 3 · answered Oct 22 '13 at 19:42

If option_key is uniqe per page_id (you don't have two or more background with page_id==1) you can do:

SELECT page.page_id, page.title, page.content,
  GROUP_CONCAT(option_key SEPARATOR '@') AS option_keys,
  GROUP_CONCAT(option_value SEPARATOR '@') as option_values,
FROM page
LEFT JOIN page_option ON page_option.page_id=page.page_id
WHERE page.page_id=USER_SPECIFIED_ID

You can execute this SQL-query and put its result into $result. After you should do every item of $result:

$result[$i]["options"] = array_combine(
        explode("@",$result[$i]["option_keys"]),
        explode("@",$result[$i]["option_values"])
);

You can do it with a foreach or you can use array_walk too.

After these you've an associative array with options in $result[$i]["options"]:

{
  "background" => "red",
  "module_key1"=> "chicken"
}

I hope it's what do you want.

Thank you for your reply. I will test this and see how efficient this is in terms of query time. — Tom Hallam, Oct 22 '13 at 19:49
Don't forget the execution time of PHP `foreach`-cycle (or `array_walk`)! If your input data is big, execution time can be big too but the SQL query time not too big :) — uzsolt, Oct 22 '13 at 19:55

Optimising SQL Queries

3 Answers3

Linked