SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Montreal
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm going to suggest you use explicit JOIN syntax like this --
    Code:
    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



    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Montreal
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm going to suggest you use explicit JOIN syntax like this --
    Code:
    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



    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


    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?


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
  •