search for value in mysql tables with less queries to the database?

Question

Hi. I have a user with his unique username in a mysql table, but I have to test and do many queries to find it. I wonder if its a better way to avoid all does queries to the db.

I have multiple rows in the table with columns like user1, user2, user3, user4 up to 30.

for ($x=0; $x < 30; $x ++){

    $user = "user"; 
    $user .= $x; //generate user1, user2, user3 etc


    $result=mysql_fetch_object(mysql_query("SELECT * FROM table WHERE ".$user."='".$_SESSION['username']."'"));
    if ($result){

Now if the $_SESSION['username'] is user30 in the table I do 29 queries before $result is true and I can work with the results. Is there a better way to do this? How important is this anyway. Is there a big difference in cpu demand for 1 query and 30 queries?

Answer 1

You shall have one column named 'user', in which you store the username. Then you only need one select to get that row:

"SELECT * FROM table WHERE user='".$_SESSION['username']."'"

That's if I understand you correct here... You have one column for each user, which is really bad design in about 100% of the cases. You must only have one user column with the username as the value.

Answer 2

While you should really have just one user column, you could use a loop to build one big query rather than doing 30 small ones.

<?php
$query = "SELECT * FROM table WHERE ";
foreach(range(1,30) as $num) {
    $query .= " user$num = '{$_SESSION['username']}'";
    if($num < 30) $query .= " OR ";
}
print $query;
$result=mysql_fetch_object(mysql_query($query));
?>

Answer 3

As Johan said, you need to normalise your database. That means removing repeated columns by creating additional tables. There are many examples of how to do this; perhaps the canonical one is the wikipedia version... http://en.wikipedia.org/wiki/First_normal_form

Answer 4

Because you're not wildcarding the search, you could perform this search in a single query:

SELECT t.* 
  FROM table t 
 WHERE '".$_SESSION['username']."' IN (t.user1, t.use2, t.user3, t.user4, t.user5,
                                                         -- rest of the columns in the same pattern
                                                         t.user26, t.user27, t.user28, t.user29, t.user30)

Is There A Big Difference Between using 1 vs 30 queries?

Yes - database queries should be considered expensive. First, there's overhead involved with the application code sending the request to the server - SQL is transmitted over TCP. The smaller the query, the shorter the TCP transmission to the server - might be milli or nanoseconds when testing a single query, but in a multi-user setup that can really add up. Then there's the overhead of the query itself - get only what you need, which means not using "SELECT *". Or why would you hit the database 30 separate times when you could do it once?

This things are hard to conceive when you're dealing with a system that is supporting yourself (or maybe a handful of people), and the application and database are on the same host (VM would be a different matter). But costs do add up.

Answer 5

You need to normalize that DB. After I made this same kind of error (user_1, ..., user_n in a single row) a few times, I learned about normalization and everything DB-related suddenly became a thousand time easier. Intro here: http://en.wikipedia.org/wiki/Database_normalization

Answer 6

I have no idea why you need to have user1...user30 in your table, but anyway, to reduce the query time, you might try the following

<?php         
     $selects = array();
     for($i=1; $i<=30; $i++) {
         $selects[] = "SELECT * FROM `table` WHERE user$i = '" . $_SESSION['username']} . "'"; 
     }

     $query = join(" UNION ", $selects);
     print $query;

UNION method is preferred as indexes (user1...user30) can be used.