Will the Where condition be better than the JOIN table condition?

Posted on 2014-08-15
Last Modified: 2014-08-15
Dear experts,

I've question with mysql query.  I want to process an online order which users will input the item number and the quantity that they wish to buy.

Will it be better that I store the item number and quantity into a temporary table and do a JOIN with the item number (item numbers are unique number) in my item table or I can select the columns that I need from item table and place all the item numbers provided by users in the where condition of a query?

User's input can be ranged from a single row to few thousand rows(maybe 10K on rare occasions).

Question by:Kinderly Wade
    1 Comment
    LVL 23

    Accepted Solution

    Well, for small sets I'd do an inline WHERE, but for large sets, I'd do the temp table.  The safe bet is the temp table.  If you have a *really* huge set of numbers, you might run into the 'max_packet_size' for MySQL when trying to do a WHERE and then it won't work at all (although this would be pretty dang rare and they'd have to have a ton of rows selected).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now