Please help speed up this ORDER BY query.

[Neo G.]

I have a database with 4 tables and about 1.5 million columns in the most populated one.

Now I do have the following query:

SELECT *, MATCH(ind_EN, ind_FR) Against('roofing*') As RelI from list l, sic s, sic_comp sc, company c WHERE
c.cid = l.cid AND l.bid = sc.bid AND s.SIC = sc.SIC AND MATCH(ind_EN, ind_FR) AGAINST('roofing*' IN BOOLEAN MODE)
ORDER BY RelI DESC, priority ASC

My problem is that because of the ORDER BY priority ASC the query is very very slow, otherwise it is extremely fast.

Since I have to first order by industry relevancy (RelI) and then by priority, could someone please help me with the query to make it faster ?

Thanks a lot!


the table structures are below:

list
---------------------------------
bid - primary key : branch number
cid - link between list and company tables
address
...
priority - prioritizes a branch of the company

company
---------------------------------
cid - primary key : company number
cname - company name
desc - description of the company

sic - industries
---------------------------------
SIC - primary key : industry number
ind_EN: name in english
ind_FR: name in french

sic_comp - industries for branch
---------------------------------
bid
SIC

no primary key in this table since 1 bid could be in different industries:


bid: 1234
SIC: 4423
bid: 1234
SIC: 5346
etc..



Edited 5 time(s). Last edit at 01/06/2009 02:28AM by Neo G..