I'm basically wondering what's the best way to query a table with such amount of rows?
I'm guessing I'd need to do some optimization before I even think about queries since anything I try to perform takes around 50 seconds to complete?
Anyone have any ideas on how to speed up my table and perform queries much quicker?
Here's an example query :
SELECT
`COMPANY_NAME`, `ZIPCODE`, `ADDRESS`
FROM
`companies`
WHERE
replace(`COMPANY_NAME`, ' ', '') = '$area'
Here's my Database structure
id int(11) PRIMARY
COMPANY_NAME varchar(255)
ADDRESS varchar(255)
CITY varchar(255)
STATE varchar(255)
ZIPCODE varchar(255)
COUNTRY varchar(255)
WEB_ADDRESS varchar(255)
PHONE_NUMBER varchar(255)
FAX_NUMBER varchar(255)
CONTACT_NAME varchar(255)
TITLE varchar(255)
GENDER varchar(255)
EMPLOYEE varchar(255)
SALES varchar(255)
MAJOR_DIVISION_DESCRIPTION text
SIC_2_Code_Description text
SIC_4_Code varchar(255)
SIC_4_Code_Description text latin1_swedish_ci
My database is also InnoDB
The key issue on performance on your query is this statement:
WHERE replace(`COMPANY_NAME`, ' ', '') = '$area'
If, instead, you had:
WHERE COMPANY_NAME = '$area'
then an index on COMPANY_NAME could be used. However, because your version uses a function on the column, the database engine has to do a full table scan instead of an index lookup.
More information would be needed to solve this problem. One would be to update COMPANY_NAME to remove spaces and add an index on it. Another would be to add another column (as suggested by @dystroy). If you are only trying to remove trailing spaces, then you probably want to update the existing column. Finally, if you are trying to match words in COMPANY_NAME, and not the whole name, then a full text index may be the best solution.
By the way, have you tried this?
WHERE COMPANY_NAME like '$area%'
That is, do a wildcard search at the end of $area. That can use an index on COMPANY_NAME and, if it returns the right results, might be the easiest way to solve your problem.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments