SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Very slow query

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Very slow query

    I have a database with over 12 million records that the user can run a search on, but it is taking far too long. Can anybody help me try and optimize my SQL to work fast? I can post any additional information that you may need. Thanks.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's hard to help you if we have to guess your circumstances

    Please post the relevant queries here and do a SHOW CREATE TABLE for each table concerned.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    East Coventry, PA
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the Table information:

    CREATE TABLE `Businesses` (
    `ID` int(11) NOT NULL auto_increment,
    `Code` varchar(11) NOT NULL,
    `Description` varchar(255) NOT NULL,
    `Company` varchar(255) NOT NULL,
    `Contact` varchar(255) NOT NULL,
    `Title` varchar(255) NOT NULL,
    `Address` varchar(255) NOT NULL,
    `City` varchar(255) NOT NULL,
    `County` varchar(255) NOT NULL,
    `State` varchar(2) NOT NULL,
    `Zip` varchar(11) NOT NULL,
    `Phone` varchar(20) NOT NULL,
    `Fax` varchar(20) NOT NULL,
    `Email` varchar(255) NOT NULL,
    `Website` varchar(255) NOT NULL,
    `Member` int(1) NOT NULL default '0',
    `Spotlight` int(1) NOT NULL default '0',
    `Logo` varchar(255) NOT NULL,
    `Spotlight_Copy` varchar(255) NOT NULL,
    `Bonus` int(1) NOT NULL default '0',
    PRIMARY KEY (`ID`),
    KEY `Member` (`Member`,`Spotlight`),
    FULLTEXT KEY `Company` (`Company`),
    FULLTEXT KEY `State` (`State`)
    ) ENGINE=MyISAM AUTO_INCREMENT=12649777 DEFAULT CHARSET=utf8

    Here is my search query:

    SELECT Company FROM Businesses WHERE MATCH (Company) AGAINST ('" & s & "') GROUP BY Company

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why are you using GROUP BY company?
    It looks unnecessary to me as it is the only thing being returned anyway. Delete it and see if you get the same data returned.

    And then try WHERE company LIKE(whatever)

    (I thought MATCH was for more complex searches than this - eg to include one thing and exclude another, but I've never actually had a use for it, so I could be wrong)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •