Union selecting, but is there a quicker way?

[renwoshin]

Hi,

 

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.

[fenway]

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.

[renwoshin]

[!--quoteo(post=330654:date=Dec 27 2005, 07:12 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 27 2005, 07:12 AM) 330654[/snapback][/div][div class=\'quotemain\'][!--quotec--]

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.

[fenway]

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.

[renwoshin]

[!--quoteo(post=330752:date=Dec 27 2005, 12:41 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 27 2005, 12:41 PM) 330752[/snapback][/div][div class=\'quotemain\'][!--quotec--]

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.

[fenway]

The "time efficiency", as you call it, would come from your WHERE clause, that would normally restrict the SELECT queries to just a particular subset of records, e.g. "WHERE type = 'cv'" or "WHERE type = 'dv'", to use your table name as examples of a flag you could set to mark the records. In this fashion, using a proper index on this column, you would be able to pull just the correct records which at the same speed (still only the 300, not the 3000), but if you needed to get the all (or groups of them), you could do it rather easily -- e.g. "WHERE type IN ('cv','dv')" -- without any UNION statements.

 

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.

 

Good luck.