Database Slow freezes Query

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

i’m going to suggest you use explicit JOIN syntax like this –

SELECT leads.listing_id 
  FROM leads
INNER
  JOIN listings 
    ON listings.id = leads.listing_id
 WHERE leads.sales_rep = 0 
ORDER 
    BY leads.listing_id ASC LIMIT 0, 10 

the reason i suggest this is because i want to distinguish between join conditions and filter conditions, and it’s obvious which is which with this syntax

the join conditions almost always join a primary key to a foreign key

primary keys get an index by default, but if you don’t actually declare the foreign key, and don’t declare an index on the column you’re using as the foreign key, then you get very poor performance as the database engine has to scan the second table for every row of the first table

in your case, you need to do this –

ALTER TABLE leads
ADD INDEX listing_ix ( listing_id )

in the WHERE clause you have another condition, and this one should have an index too –

ALTER TABLE leads
ADD INDEX sales_rep_ix ( sales_rep )

by the way, note that sales_rep is numeric, so you should not have quotes around the 0 in the query

let me know if that improves performance

and thanks, by the way, for including your CREATE TABLE statements, i wish more people did that

:slight_smile:

p.s. you will notice i removed one of the columns from the SELECT clause, and changed the column in the ORDER BY – see if you can guess why

:slight_smile:

Hi Thanks for the help, you are truly a DB Guru!
It worked out great. If you have one of those “Paypal buy me a coffee” buttons I would click on it.

I am not really sure why?