List:General Discussion« Previous MessageNext Message »
From:Alex Krohn Date:July 10 1999 8:29pm
Subject:query optimization..
View as plain text  
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

Thread
query optimization..Alex Krohn11 Jul
  • Re: query optimization..Tõnu Samuel11 Jul
    • Re: query optimization..Paul DuBois11 Jul
  • Re: query optimization..Tõnu Samuel14 Jul
Re: query optimization..Alex Krohn11 Jul
  • Re: query optimization..Christian Mack17 Jul