Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Sql_big_selects

  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: Sql_big_selects

    I've the following query:

    Code:
    SELECT a.field1, b.field2, b.field3
    FROM table1 a
    JOIN table2 b ON a.field4 = b.field5
    JOIN table3 c ON b.field6 = c.field7
    JOIN table4 d ON a.field8 = d.field9
    WHERE a.field8 = 1
    AND d.field10 =  "Roger"
    AND c.field11 =  "Rabbit"
    ORDER BY a.field1 DESC
    This will now generate the SET SQL_BIG_SELECTS=1 error message.

    I have looked at my query but can't see what's wrong. It works fine with using the big selects statement.

    Why do I have to do this?

    Thanks for any help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    I have looked at my query but can't see what's wrong.
    neither can we, because you've obfuscated the column names

    obviously, the syntax is okay

    It works fine with using the big selects statement.
    probably doing table scans, generating tons of intermediate rows

    how's the performance?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    44
    Thanks for quick reply!

    Quote Originally Posted by r937 View Post
    neither can we, because you've obfuscated the column names

    obviously, the syntax is okay

    probably doing table scans, generating tons of intermediate rows

    how's the performance?
    Does it matter if I changed the column names?

    Sorry, but I'm a bit novice at this, what does table scans, generating tons of intermediate rows mean? And the performance, do you mean how fast the query executes in phpMyAdmin?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    And the performance, do you mean how fast the query executes in phpMyAdmin?
    yeah... how is it?

    and how many rows does the query produce?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    yeah... how is it?

    and how many rows does the query produce?
    It could vary, but the test I did now took around 0.009 sec and produced 4 rows.

    In another similar query (just a couple of more where conditions), it took around 0.014 sec and produced 186 rows.

    That's not very much right?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's very acceptable

    hmm, i wonder what that sql_big_selects was all about...

    could you do an EXPLAIN on the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    no, that's very acceptable

    hmm, i wonder what that sql_big_selects was all about...

    could you do an EXPLAIN on the query?
    Yes, that worked. Not sure how I should post it here though, it's a table format. Should I take a screenshot?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whatever works best for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2012
    Posts
    44
    Ok, been busy but here I am wanting help again with this, I really don't know what's wrong!

    I have attached a screenshot of the EXPLAIN. There was also a little typo in my SQL, but I'm not sure that matters, I'll post it again.

    Code:
    SELECT a.field1, b.field2, b.field3
    FROM table1 a
    JOIN table2 b ON a.field4 = b.field5
    JOIN table3 c ON b.field6 = c.field7
    JOIN table4 d ON c.field8 = d.field9
    WHERE a.field8 = 1
    AND d.field10 =  "Roger"
    AND c.field11 =  "Rabbit"
    ORDER BY a.field1 DESC
    Thanks!
    Attached Thumbnails Attached Thumbnails sql_explain.png  

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I see no indexes on tables a & c
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by healdem View Post
    I see no indexes on tables a & c
    A has a PRIMARY, and it seems I have an INDEX on table C..

    What should I set it to? (Probably a stupid question, sorry.)

  12. #12
    Join Date
    Feb 2012
    Posts
    44
    Can I please ask what's most important to think about regarding PRIMARY and INDEX? If I have a table with a PRIMARY key (with unique values), is that enough for that table, or should I have INDEX as well, and if so what kind of columns should that be used in?

    Sorry if it's a stupid question, I'm trying to learn...

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your explain shows that the query is not using any index on tables a & c
    so its doing a table scan for both tables

    in a realtional db every table should have something that that uniquely identifes the row, that is usually indexed and is called (in MySQL) PRIMARY, and is the primary key. all a primary key is is a an index that only allows unique values. if you so wished you could also declare another index which acted like a primary index (ie declare is as unique) but you can only ever have one primary index.

    as to what columns you elect to index that depends on your requirements. indexes are not a free lunch, they come with strings attached (require more disk space, slower on inserts or updates (of indexed columns) as more work has to be doen to keep the indexes up to date. however indexes can also speed up access when retrieving data. there is no hared and fast rule as to when you should index columns. I suspect the closest you'll get to one is if the column is often used in a where or join clause then its a candidate for indexing.

    part of the process of being a DBA is tuning and modifying the db through judicious use of indexes and so on based on empirical evidence that there is a perfomance problem.

    but you don't have that empirical evidence that there is a performance problem

    we don't know what your columns are called so we can't make guesses about whether some columns should or should not be indexed

    ferinstace if this is a once in a blue moon query or the number of rows in table a isn't that much then mebbe you don't need to consider indexing a.field8

    if your table was, say persons
    then I'd want a primary key, probably a key on the surname, first names. I wouldn't index gender, depending on the application requirements I might index on dateofbirth.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2012
    Posts
    44
    Thanks a lot Healdem, appreciate it!!

    I have know got more knowledge about this, and I have INDEX'ed some of my tables.

    My query now works without any problems, no need for BIG SELECTS anymore.

    I have never really learned about indexing, and all I have set before is a PRIMARY at the id of the table, and most often used AUTO_INCREMENT as well.

    Can I please ask follow-through questions now?

    In the EXPLAIN there's a row called type. When I run an EXPLAIN on my query now, 3 of the tables have ref, and 1 has eq_ref. What does this stand for?

    Only thing I found so far is an article that said this:

    type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
    Is that correct? Why, and what does those mean?

    Thanks!!

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Haydn View Post
    Thanks a lot Healdem, appreciate it!!

    I have know got more knowledge about this, and I have INDEX'ed some of my tables.

    My query now works without any problems, no need for BIG SELECTS anymore.

    I have never really learned about indexing, and all I have set before is a PRIMARY at the id of the table, and most often used AUTO_INCREMENT as well.

    Can I please ask follow-through questions now?

    In the EXPLAIN there's a row called type. When I run an EXPLAIN on my query now, 3 of the tables have ref, and 1 has eq_ref. What does this stand for?

    Only thing I found so far is an article that said this:



    Is that correct? Why, and what does those mean?

    Thanks!!
    as ever 'da manuel' is your matey
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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