Hi
I am running a query in a database and the database then freezes.
Any ideas on how to make this query more efficient? I have been struggling with this for 2 days.
There is about 60 000 records in the table.
Here is the query:
SELECT listings.id, leads.listing_id FROM
leads
,listings
WHERE leads.listing_id = listings.id AND leads.sales_rep = ‘0’ ORDER BY id asc LIMIT 0, 10
LEADS
CREATE TABLE IF NOT EXISTS
leads
(
id
bigint(12) NOT NULL AUTO_INCREMENT,
listing_id
bigint(12) NOT NULL,
sales_rep
smallint(6) NOT NULL,
source
smallint(6) NOT NULL,
state
smallint(6) NOT NULL,
insert_date
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
converted_date
timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
payed_date
timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
status
varchar(30) NOT NULL DEFAULT ‘0’,
return_client
tinyint(1) NOT NULL,
credit_card_number
varchar(20) NOT NULL,
credit_card_code
varchar(10) NOT NULL,
expire_date
varchar(10) NOT NULL,
credit_card_type
smallint(6) NOT NULL,
PRIMARY KEY (id
)
)
LISTINGS
CREATE TABLE IF NOT EXISTS listings
(
id
bigint(12) NOT NULL DEFAULT ‘0’,
user_id
bigint(12) NOT NULL DEFAULT ‘0’,
company_name
varchar(80) NOT NULL DEFAULT ‘’,
friendly_url
varchar(80) NOT NULL,
category
smallint(6) NOT NULL DEFAULT ‘0’,
website
varchar(255) NOT NULL DEFAULT ‘’,
description
text NOT NULL,
listing_type
tinyint(4) NOT NULL DEFAULT ‘0’,
listing_type_leads
tinyint(4) NOT NULL,
leads_price
decimal(10,0) NOT NULL DEFAULT ‘0’,
address_line1
varchar(50) NOT NULL DEFAULT ‘’,
address_line2
varchar(50) NOT NULL DEFAULT ‘’,
city
varchar(30) NOT NULL DEFAULT ‘’,
state
smallint(6) NOT NULL DEFAULT ‘0’,
country
smallint(6) NOT NULL DEFAULT ‘0’,
zipcode
varchar(7) NOT NULL DEFAULT ‘’,
phone
varchar(15) NOT NULL DEFAULT ‘’,
fax
varchar(15) NOT NULL DEFAULT ‘’,
toll_free
varchar(15) NOT NULL DEFAULT ‘’,
last_edit
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
active
tinyint(1) NOT NULL DEFAULT ‘0’,
meta_title
text NOT NULL,
meta_description
text NOT NULL,
meta_keywords
text NOT NULL,
deleted
tinyint(4) NOT NULL,
template
tinyint(2) NOT NULL,
KEY country_ix
(country
)
)
Thanks for any advice in advance.
Chris