Query Across 5 Tables

[Adeus]

Hi, I am using MySQL 4.1.22. I have a 4GB database with about 20 million records. I'm somewhat new to querying such large databases (and MySQL in general). I apologize in advance for using the cliché address book example, but - that's what it is!

 

I have these five tables:

 

 

CREATE TABLE `tblDataAllNormal` (

  `DATAID` int(10) NOT NULL auto_increment,

  `CompanyNameID` int(10) default NULL,

  `ADDRESS` varchar(100) default NULL,

  `CityID` int(10) default NULL,

  `StateID` int(10) default NULL,

  `ZIP` varchar(10) default NULL,

  `WEB` varchar(50) default NULL,

  `PHONE` varchar(50) default NULL,

  `FAX` varchar(50) default NULL,

  `SICID` int(10) default NULL,

  PRIMARY KEY  (`DATAID`),

  KEY `IX_CityID` (`CityID`),

  KEY `IX_CompanyNameID` (`CompanyNameID`),

  KEY `IX_CompanyNameID_CityID_SICID` (`CompanyNameID`,`CityID`,`SICID`),

  KEY `IX_SICID` (`SICID`),

  KEY `IX_SICID_CompanyNameID` (`SICID`,`CompanyNameID`),

  KEY `IX_StateID` (`StateID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

 

CREATE TABLE `CompanyName` (

  `CompanyNameID` int(10) NOT NULL auto_increment,

  `CompanyName` varchar(75) default NULL,

  PRIMARY KEY  (`CompanyNameID`),

  KEY `IX_CompanyName` (`CompanyName`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

CREATE TABLE `City` (

  `CityID` int(10) NOT NULL auto_increment,

  `City` varchar(50) default NULL,

  PRIMARY KEY  (`CityID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

CREATE TABLE `State` (

  `StateID` int(10) NOT NULL auto_increment,

  `State` char(2) default NULL,

  PRIMARY KEY  (`StateID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

CREATE TABLE `SIC` (

  `SICID` int(10) NOT NULL auto_increment,

  `Code` varchar(7) default NULL,

  `Industry` varchar(150) default NULL,

  PRIMARY KEY  (`SICID`),

  KEY `IX_Code` (`Code`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

 

The query I am trying is (I know it's probably very ugly);

 

SELECT CompanyName.CompanyName AS CompanyName,

tblDataAllNormal.PHONE AS PHONE,

tblDataAllNormal.ADDRESS AS ADDRESS,

City.City AS City,

State.State AS State,

tblDataAllNormal.ZIP AS ZIP,

SIC.Industry AS Industry

FROM CompanyName, tblDataAllNormal, City, State, SIC

WHERE CompanyName LIKE '%something%'

ORDER BY CompanyName ASC

LIMIT 0,10;

 

All I want to do is select so I can list the records accordingly;

 

CompanyName

Phone

Address

City

State

Zip

Industry

 

Please advise. Shall I dabble more in JOINs? Or is there a quick fix?

[Barand]

FROM CompanyName, tblDataAllNormal, City, State, SIC

WHERE CompanyName LIKE '%something%'

 

Joining tables without specifiying which columns to join on results in every record in each table being joined with all records in the other tables. So if you have only 100 records in each table your query result will have

 

100 x 100 x 100 x 100 x 100 rows (10,000,000,000).

 

With the number of records you have it will be running till the next ice age.

 

Specify the joins

 

... FROM CompanyName 
         INNER JOIN tblDataAllNormal ON CompanyName.CompanyNameID = tblDataAllNormal.CompanyNameID
         INNER JOIN City ON tblDataAllNormal.CityID = City.CityID 

              etc

WHERE CompanyName LIKE '%something%'
ORDER BY CompanyName ASC
LIMIT 0,10;

[Adeus]

Thank you very much Barand. So should it look like this?:

 

SELECT CompanyName.CompanyName AS CompanyName,
tblDataAllNormal.PHONE AS PHONE,
tblDataAllNormal.ADDRESS AS ADDRESS,
City.City AS City,
State.State AS State,
tblDataAllNormal.ZIP AS ZIP,
SIC.Industry AS Industry
FROM CompanyName
  INNER JOIN tblDataAllNormal ON CompanyName.CompanyNameID = tblDataAllNormal.CompanyNameID
  INNER JOIN City ON tblDataAllNormal.CityID = City.CityID
  INNER JOIN State ON tblDataAllNormal.StateID = State.StateID
  INNER JOIN SIC ON tblDataAllNormal.SICID = SIC.SICID
WHERE CompanyName LIKE '%marketing%'
ORDER BY CompanyName ASC
LIMIT 0,10;

 

It seems this query works fine (under a second) until I add something more to it. For example:

 

SELECT CompanyName.CompanyName AS CompanyName,
tblDataAllNormal.PHONE AS PHONE,
tblDataAllNormal.ADDRESS AS ADDRESS,
City.City AS City,
State.State AS State,
tblDataAllNormal.ZIP AS ZIP,
SIC.Industry AS Industry
FROM CompanyName
  INNER JOIN tblDataAllNormal ON CompanyName.CompanyNameID = tblDataAllNormal.CompanyNameID
  INNER JOIN City ON tblDataAllNormal.CityID = City.CityID
  INNER JOIN State ON tblDataAllNormal.StateID = State.StateID
  INNER JOIN SIC ON tblDataAllNormal.SICID = SIC.SICID
WHERE CompanyName LIKE '%marketing%'
AND State = 'CA'
ORDER BY CompanyName ASC
LIMIT 20,10;

 

When I add the "AND State = 'CA'", it times the query out. Should I apply the first INNER JOIN to State.StateID as well?

[Barand]

If you can, use

 

... AND tblDataAllNormal.StateID = 'something'

 

 

You have an index on that.

[Adeus]

I tried

 

... AND tblDataAllNormal.StateID = '10'

 

and it still lost the connection after a few minutes of trying. Also, if I use the query that worked except with LIMIT 10,10, it times out as well. Hmmm...

[Barand]

Plan B

 

Try adding an index on (CompanyNameID, StateID)

[Adeus]

Sorry, as I said I'm still new to these kinds of large queries. How exactly do I create such an index?

[Barand]

ALTER TABLE `tblDataAllNormal` ADD INDEX `company_state` (CompanyNameID, StateID);

[Adeus]

Unfortunately, the index did not help. I am trying something new, a friend advised me not to use INNER JOIN, as it creates a new temporary table which will slow things down. Is this accurate?

 

Here is what I am trying. It takes 92 seconds, but retrieves the records. So it is working but something is horribly wrong.

 

SELECT CompanyName.CompanyName, tblDataAllNormal.PHONE, tblDataAllNormal.ADDRESS, City.City, State.State, tblDataAllNormal.ZIP, SIC.Industry
FROM CompanyName, tblDataAllNormal, City, State, SIC
WHERE CompanyName.CompanyName LIKE '%Something%'
AND State.State = 'OH'
AND tblDataAllNormal.ZIP LIKE '54040%'
AND SIC.Industry LIKE '%something%'
AND CompanyName.CompanyNameID = tblDataAllNormal.CompanyNameID
AND tblDataAllNormal.CityID = City.CityID
AND State.StateID = tblDataAllNormal.StateID
AND tblDataAllNormal.SICID = SIC.SICID
ORDER BY CompanyName ASC
LIMIT 0,10;

 

I am getting desperate here. I will PayPal someone $40 to help me find the right query. I can provide the host/user/pw details if it would help.

 

The query I am looking for will do this:

It will return: CompanyName, Phone, Address, City, State, Zip, Industry

Depending on search criteria (any combination of): CompanyName, City, State, Zip, Industry

[Barand]

 

a friend advised me not to use INNER JOIN, as it creates a new temporary table which will slow things down. Is this accurate?

 

If the INNER JOIN version took less than a second, it's not exactly slow

[Adeus]

If the INNER JOIN version took less than a second, it's not exactly slow

 

And I know not to trust this friend of mine with database-related issues anymore. I did not mean to bash your method, Barand. Just throwing some input/options in. Going to keep trying...

[aschk]

Put "DESRCIBE" in front of your SQL statement before you process it (probably best to run this from the command line) and let us know what your output is. This will tell you what indexes you are using and also what JOINs are being performed on which columns, and also IF you are actually using a temporary table.

[Adeus]

Here is the ultimate query I am going for - users will be able to search using any or all of the criteria.

 

DESCRIBE SELECT CompanyName.CompanyName AS CompanyName,
tblDataAllNormal.PHONE AS PHONE,
tblDataAllNormal.ADDRESS AS ADDRESS,
City.City AS City,
State.State AS State,
tblDataAllNormal.ZIP AS ZIP,
SIC.Industry AS Industry
FROM CompanyName
  INNER JOIN tblDataAllNormal ON CompanyName.CompanyNameID = tblDataAllNormal.CompanyNameID
  INNER JOIN City ON tblDataAllNormal.CityID = City.CityID
  INNER JOIN State ON tblDataAllNormal.StateID = State.StateID
  INNER JOIN SIC ON tblDataAllNormal.SICID = SIC.SICID
WHERE CompanyName LIKE '%marketing%'
AND State = 'CA'
AND ZIP LIKE '54040%'
AND Industry LIKE '%something%'
ORDER BY CompanyName ASC
LIMIT 0,10;

 

Attached is the DESCRIBE.

 

 

[attachment deleted by admin]

[aschk]

Ok, from this I can tell you you're doing a lookup on 9051 rows in the SIC table (you see in your diagram).

Thus it appears the analyser isn't using a key on your SIC table (despite there being a primary key).

 

I do find it interesting however that you're telling me the part of the query causing the issue is the State column...

 

Perhaps noteworthy is the usage of the "LIKE" clause. I would try and restrict this as much as possible because it's nearly impossible to use an index when you're doing a "%LIKE%".

 

I'm curious to know what your database structure is becuase from what i've seen you've separated out CompanyName from the normal company information (tblDataAllNormal). Is there some reasoning here?

If possible could you explain what SIC means, and what data it holds? As far as I know it just holds a list of industries.

 

note: I've read (somewhere) that in MySQL "INNER JOIN" and "JOIN" algorithmically work the same.

 

 

oops: should have re-read your first post... you've supplied all the tables. Let me have a look.

[Adeus]

I wonder why it is not using a key on my SIC table. The key is there. Is it a syntax error in my query?

 

I dropped the %LIKE% and it helped, but is still taking over 20 sec. Going to keep trying...

[aschk]

If it was a syntax error the query would never run.