MYSQL speed up query

I am writing reports for my school's lunch order program, I have made a query that gives me the results I need but is very very slow. I was thinking of using correlated sub queries to speed up (suggestion from another similar thread) but can't get past syntax errors and am not even sure if it will accomplish what I want.

The two tables involved are Ordered_Items and Ordered_Options the Ordered Items table would generally have 3 - 4 related records in the Ordered Options table which represent the options the students choose when ordering lunch.

A typical record would look like this

Ordered_Items.Record_Number = 1
Ordered_Items.Name = Pizza

Ordered_Options.Ordered_Items_rn = 1
Ordered_Options.Value = Jane
Ordered_Options.rn = 43

Ordered_Options.Ordered_Items_rn = 1
Ordered_Options.Value = Doe
Ordered_Options.rn = 44

Ordered_Options.Ordered_Items_rn = 1
Ordered_Options.Value = Pepperoni
Ordered_Options.rn = 45

I would like them all to show up as a single record for reporting purposes so the output from the query would look like: Pizza Jane Doe Pepperoni. The Ordered_Options.rn are consistent meaning that the first name is always = 44 and so on. I have put my code below.

SELECT
  OI.Name AS ItemName,
  opt1.`Value` AS StudentFirst,
  opt2.`Value` AS StudentLast,
  opt3.`Value` AS Grade,
  opt4.`Value` AS Milk
FROM
  ((((
  Ordered_Items OI
  LEFT JOIN Ordered_Options opt1 ON ((
            (OI.record_number = opt1.Ordered_Items_rn)
         AND(opt1.Options_rn = 43))))
  LEFT JOIN Ordered_Options opt2 ON((
 开发者_StackOverflow社区           (OI.record_number = opt2.Ordered_Items_rn)
         AND(opt2.Options_rn = 44))))
  LEFT JOIN Ordered_Options opt3 ON ((
            (OI.record_number = opt3.Ordered_Items_rn)
     AND(opt3.Options_rn = 46))))
  LEFT JOIN Ordered_Options opt4 ON ((
            (OI.record_number = opt4.Ordered_Items_rn)
         AND(opt4.Options_rn = 55))))

First, as others have said if you have the possibility you should really revise the structure of your database, what you have now doesn't make much sense.

However I've been in that "having to do reports from existing code" situation so I know that you might not be able to do that at all; if that is indeed your case then the following query should help you significantly improve speed:

SELECT 
  `Record_Number`, 
  Concat(Name, ' ', Group_Concat(Value ORDER BY rn ASC SEPARATOR ' ')) AS Request
FROM 
  `Ordered_Items` oi 
  LEFT OUTER JOIN `Ordered_Options` oo ON (oo.Ordered_Items_rn = oi.Record_Number) 
GROUP BY 
  oi.Record_Number

Which should give you something like:

Record_Number   Request
1               Pizza Jane Doe Pepperoni
2               Fries Bobby Table French

Since you are new around here, please remember to accept as a solution by cliking on the arrow on the left is this fixes your problem. If it doesn't, please indicate why in a comment to this answer.

Sample database I used for it:

--
-- Table structure for table `Ordered_Items`
--    
CREATE TABLE IF NOT EXISTS `Ordered_Items` (
  `Record_Number` int(11) NOT NULL,
  `Name` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Ordered_Items`
--

INSERT INTO `Ordered_Items` (`Record_Number`, `Name`) VALUES
(1, 'Pizza'),
(2, 'Fries');

-- --------------------------------------------------------

--
-- Table structure for table `Ordered_Options`
--

CREATE TABLE IF NOT EXISTS `Ordered_Options` (
  `Ordered_Items_rn` int(11) NOT NULL,
  `Value` text NOT NULL,
  `rn` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Ordered_Options`
--

INSERT INTO `Ordered_Options` (`Ordered_Items_rn`, `Value`, `rn`) VALUES
(1, 'Jane', 43),
(1, 'Doe', 44),
(1, 'Pepperoni', 45),
(2, 'Bobby', 43),
(2, 'Table', 44),
(2, 'French', 45);

A few comments:

• Normalize your table, no amount of tricks will speed up this unnormalized mess to acceptable proportions.
• Get out of the ` backtick habit, it's an eyesore. You only need to backtick reserved words or column names with -gasp- spaces.
• You don't need to use () parentheses to this extend, as long as only use AND and no subselects you don't need any.
• Indenting is good, indenting using tab is bad, especially when pasting code on stackoverflow.

With regards to your table.

Throw out the one you have now and create a new table.

table ordered_item
-----------
id integer auto_increment primary key  
item_id integer not null foreign key references item(id) on delete restrict on update cascade
customer_id integer not null foreign key references customer(id) on delete restrict on update cascade
qty integer not null
.....

table customer
--------------
id integer auto_increment primary key    
firstname varchar(200)
.....

table item
----------
id integer auto_increment primary key  
name varchar(200)
price decimal(10,2)  //should really be in a separate table

The following query will give all items ordered per order with a grand total for customer 1124.

SELECT customer.name, io.id as order_id, sum(io.qty) as number_of_items, i.* 
FROM item i
INNER JOIN ordered_item oi ON (oi.item_id = i.id)
INNER JOIN customer c ON (c.id = oi.customer_id)
WHERE customer.id = '1124'
GROUP BY io.id WITH ROLLUP

This is the way SQL is supposed to work, not the way you are doing it.

BTW the brackets are optional, I just like 'm for readability.

I agree that this would be vastly easier with the appropriate database structure, but will answer assuming you can't change it. My answer is similar to Lepidosteus', but gives you a result with values in separate columns, rather than concatenated together. It starts with a subquery using the if operator.

select oi.Record_Number as Record_Number, oi.Name as Name,
    if (oo.rn = 43, oo.Value, null) as firstName,
    if (oo.rn = 44, oo.Value, null) as lastName,
    if (oo.rn = 45, oo.Value, null) as topping
from Ordered_Items as oi join Ordered_Options as oo 
    on oi.Record_Number = oo.Ordered_Items_rn;

This gives a table with multiple rows for each record in Ordered_Items, each of which has the correct value for one of the data columns from Ordered_Options, and null in the other data columns from Ordered_Options.

|Record_Number |Name   |firstName |lastName |topping   |
--------------------------------------------------------
|1             |Pizza  |Jane      |null     |null      |
|1             |Pizza  |null      |Doe      |null      |
|1             |Pizza  |null      |null     |Pepperoni |

Now wrap this subquery in a query that groups all the records for a single Ordered_Item row together, and uses the group_concat operator to collect the value for each data column from Ordered_Options (ignoring the nulls).

select c.Record_Number, c.Name,
    group_concat(c.firstName) as firstName,
    group_concat(c.lastName)  as lastName,
    group_concat(c.topping)   as topping
from
(select oi.Record_Number as Record_Number, oi.Name as Name,
    if (oo.rn = 43, oo.Value, null) as firstName,
    if (oo.rn = 44, oo.Value, null) as lastName,
    if (oo.rn = 45, oo.Value, null) as topping
 from Ordered_Items as oi join Ordered_Options as oo 
    on oi.Record_Number = oo.Ordered_Items_rn) as c
group by c.Record_Number, c.Name;