Union selecting, but is there a quicker way?
Posted 27 December 2005 - 08:18 AM
I was wondering how you could search through all the entries in the tables in the database and have a complete list all in one query. The only way I know how to do is this:
$request = "SELECT * FROM cp WHERE email='" . $_SESSION['email']. "' " .
"UNION SELECT * FROM dv WHERE email='" . $_SESSION['email']. "' ORDER BY log DESC";
and keep on adding the "union select" for all 10 tables I have. All 10 tables have the same column names.
Is there a quicker way to search for one thing through the whole database? Thanks a bunch.
Posted 27 December 2005 - 03:12 PM
Hope that helps.
Posted 27 December 2005 - 06:23 PM
Not really... if they're in separate tables, you have to run through each one; in principle, you could generate a view in MySQL 5 if you're planning on doing this very often. Furthmore, make sure that your ORDER BY clause applies to the result set _after_ all of the UNIONs -- if you have subselect support, you can easily do this enclosing the entire set of 10 tables + UNION statement in parenthesis, and then issue the order by on the derived table. Otherwise, the parser might get confused. Lastly, if you have do this for many queries, this may be an indication that all these data should reside in a single table with a type flag.
Hope that helps.
Thanks for the reply man. I guess I will have to design my database differently.
Posted 27 December 2005 - 08:41 PM
Posted 28 December 2005 - 12:05 AM
I guess I have the same question for you as I had for another poster -- what's the basis for keeping the table separate? If it doesn't help, and make thing easier, then you've added unnecessary duplication of fields among tables, and you run into the types of problems you've discovered.
oh ok. I just started using mySQL and i have no idea how it searches the table when you query for something. I kept them separate because by using an if statement, I could choose which table to search from. Since mySQL HAS to go through all the elements, if I separated them out, each query would just have to go through 300 rows instead of 3000 rows.
Just trading space efficiency for time efficiency, but I'm guessing the time that I save is negligible?
And btw, I'm that other poster you're talking about. Thanks.
Posted 28 December 2005 - 12:22 AM
Hope that makes sense -- almost all searches are done using an index, and there are many resources you can consult if you're having trouble with those concepts (including this forum!). I can give many more examples that are specific to your particular scenario if need be.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users