Hi!
If I have two tables like:
CREATE TABLE Data (
ID INT PRIMARY KEY,
Category INT NOT NULL,
# ... Other fields not relevant
INDEX ndx (Category)
)
CREATE TABLE Alt (
DataID INT NOT NULL,
Category INT NOT NULL,
INDEX ndx (DataID),
INDEX ndx1 (Category)
)
The first table is just a data table with an id number, a category
number and a list of other fields. The second table is a list of alternate
categories the Data is in. Is it possible to get a list of data items in a
given category using this schema? I've come up with:
SELECT d.*
>FROM Data as d LEFT JOIN Alt as a ON d.ID = a.DataID
WHERE d.Category = 5 OR a.Category = 5
However this is not too good:
+-------+------+---------------+--------+---------+------+-------+-----------
-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+--------+---------+------+-------+-----------
-+
| d | ALL | ndx | NULL | NULL | NULL | 27517 | |
| a | ref | ndx | ndx | 4 | d.ID | 10 | where used |
+-------+------+---------------+--------+---------+------+-------+-----------
-+
as a full scan of the data table is required. I need to do it all in one query
as I need to sort the results. I can easily pull the info out using two
queries, but then I have to sort the two result sets back together.
Any ideas would be really appreciated!
Cheers,
Alex
