Results 1 to 16 of 16
  1. #1
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Compare Mysql Tables Efficiently
    I have 2 tables. TableA is my master table of products and TableB is my update table of products.

    Both TableA and TableB have the exact same structure. Both tables have around 60K records.

    I installed Apache, PHP, Mysql and PHPMyAdmin on my desktop and an running the following mysql query from command line.

    The simple query checks to see if there are any products in TableB that are not included in TableA. If prodid exists in TableB but not TableA, the idx value on TableB should be displayed.

    Code:
    SELECT TableB.idx 
    FROM TableB 
    LEFT JOIN TableA 
    ON TableB.prodid=TableA.prodid 
    WHERE TableA.prodid IS NULL;
    Given all that, I executed an file.sql with the above select statement on my desktop from command line and it's still running after 20 minutes, which I think is crazy.

    Either my query is really bloated or there are too many records to compare.

  2. #2
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    SB, did you try entering your query in the Run SQL box in phpMyAdmin?

  3. #3
    Full Member iolaire's Avatar
    Join Date
    October 3rd, 2006
    Location
    Arlington, VA
    Posts
    229
    You will need to learn about the EXPLAIN command in MySQL

    Code:
    EXPLAIN SELECT TableB.idx 
    FROM TableB 
    LEFT JOIN TableA 
    ON TableB.prodid=TableA.prodid 
    WHERE TableA.prodid IS NULL;
    That will tell you how many rows it will look through and what index if any your query is using.

    At a minimum it should be using an index on each prodid column.

    Long term what you are trying to do is have the query use good indexes so that it doesn't need to do things like file sort, or create temp tables and such.

  4. #4
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Definitely make sure you've got an index on prodid for both tables. The problem with this sort of query is that it can take a little while to process as you've seen. It's sometimes better to take the more laborious approach and go through each row one at a time. It might seem excessive because it's quite a few queries, but it can be better to do thousands of queries than a single query that could lock your site for several minutes.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  5. #5
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    Definitely something wrong there. That SELECT should be a fraction of a second if things are set up right.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  6. #6
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    My bad. Column prodid did not have an index. It's a bit faster now.

    Query took 0.0276 sec)

  7. #7
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Quote Originally Posted by SeymourButts
    Query took 0.0276 sec)
    Ha Ha, that's just a teeny bit faster!

  8. #8
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    Consider install SQLyog (free from http://webyog.com/en/) so you don't have to drop into the command line. Nice GUI for running queries, dealing with tables/columns, etc.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  9. #9
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    I am all set now. I have a few crons that...

    1) backup the master table
    2) build a temp table from the latest datafeeds
    3) update price for each item in master table
    4) add items from temp table that are not in master table
    5) add latest prices into price table


    Collectively, this all takes about 15 seconds.

  10. #10
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Quote Originally Posted by SeymourButts
    Collectively, this all takes about 15 seconds.
    Just wait till you've got 1m prices to update =)
    Hatred stirs up strife, But love covers all transgressions.

  11. #11
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Is there some benefit to using two tables? A main and temp table? I have always taken a datafeed, parsed it with a query based on product id and merchant, if it isn't in the table, the record gets added to the table. If the query found the product id / merchant combination it would update the fields in that record.

    Sorry, about taking this off topic, but seems like that would be a quicker method than using two tables.
    Someday starts today
    Military Discounts

  12. #12
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    knight, it's a scalability benefit. If you've only got one table your fulltext index will slow down the update process and the update process will slow down your front end. Then if you're doing price comparison, you only need one row for any number of prices. Your price table could have 1m+ rows while your search table could have only 500k. Ideally you'll eventually want to replace your mySQL fulltext search table with a dedicated fulltext search engine. These are hundreds of times faster than mySQL's fulltext search.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  13. #13
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Changing to get all colums from TableB returns a mysql error.

    Code:
    SELECT TableB.* 
    FROM TableB 
    LEFT JOIN TableA 
    ON TableB.prodid=TableA.prodid 
    WHERE TableA.prodid IS NULL;
    MySQL client ran out of memory

  14. #14
    Full Member iolaire's Avatar
    Join Date
    October 3rd, 2006
    Location
    Arlington, VA
    Posts
    229
    You might be bring back a huge result set. You can change the code to an inner join to remove the where statment:
    Code:
    SELECT TableB.* 
    FROM TableB 
    INNER JOIN TableA 
    ON TableB.prodid=TableA.prodid  ;
    but before you do that figgure out how many records you will bring back
    Code:
    SELECT count(*) 
    FROM TableB 
    INNER JOIN TableA 
    ON TableB.prodid=TableA.prodid  ;

  15. #15
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    I need the where statement since I am finding records in TableB that are not in TableA.

  16. #16
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    SB, I have always been told to stay away from *, instead request the names specifically to make your query more efficient.

    Or, maybe adding a second query to get the details of the product once you have the id would be better.

  17. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Compare+ WP theme
    By sdanpo in forum Blogging, Mobile and Social Media
    Replies: 11
    Last Post: October 16th, 2012, 11:14 AM
  2. Using time effectively, not just efficiently
    By Vendzilla in forum Midnight Cafe'
    Replies: 2
    Last Post: March 20th, 2012, 07:14 PM
  3. Search the Web More Efficiently - Article Bazac
    By bazac in forum Search Engine Stuff
    Replies: 3
    Last Post: October 30th, 2003, 06:37 PM
  4. Compare LS to CJ please
    By smoothcorp in forum Rakuten LinkShare - LS
    Replies: 18
    Last Post: November 7th, 2002, 11:12 AM

Posting Permissions

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