Mysql Query taking too much time. Almost 10 minutes

[boomarang]

Hello Friends,

 

Please help me to minimise the time taking to run the mysql query.

 

I have two tables table1 and table2.

 

I want to join two tables. The first table is having around 3000 records and the second table consists of around 6000 records.

 

This is my query:-

 

$sql = "SELECT * FROM TABLE1 WHERE TYPE='med' AND SUBMISSION_TYPE='submission'";

$query = mysql_query($sql);

$i=0;

while($row=mysql_fetch_array($sql)) {

      $query1=mysql_query("SELECT * FROM TABLE2 WHERE tab_id='".$row['id']."'");
      while($row1=mysql_fetch_array($query1))
      {
          $valueArr[$i]['values'][] = $row1;
      }

      $i++;

 

 

 

Regards,

 

[Barand]

The first thing to say is don't run queries inside loops - extremely inefficient. Run a single query with a JOIN.

 

The second is don't use SELECT *. Specify the individual column that you want to retrieve. The query time is proportional to the amount of data retrieved so only get what you need.

 

It seems you are only really interested in data from table2 (I have to use * as I don't know your columns)

$sql = "SELECT t2.*
    FROM 
        table1 t1
        INNER JOIN table2 t2 ON t1.id = t2.tab_id
    WHERE t1.type = 'med' AND t1.submission_type = 'submission'";

$query = mysql_query($sql);
while ($row = mysql_fetch_assoc($query)) {
    $valueArr[] = $row;
}

[KaiSheng]

1) Do not use SELECT * FROM wherever in a query.

2) Follow Barand coding ^

[dalecosp]

Barand knocked it, and gets my vote.  The title of the thread should probably be "10 minutes to run 18 million queries ... I've got a great server!" ;)

And I know we may be a tad over-zealous and have killed PHPBuilder by saying this, but, for goodness' sake, it's 2013.  mysql_query, mysql_fetch_assoc, mysql_connect, and all the mysql extension functions have been deprecated.  Time to use mysqli instead, or PDO ....

Edited November 14, 2013 by dalecosp

[Pawan_Agarwal]

Suppose, I have a table that has one million rows in it. That particular table has information related to people of particular country. It has few fields like user_id, name, phone, address, email address and some relevant fields like that. I am keeping user_id as primary key [which is unique]. Table name is people_record. There is no join or stuff like that, I am asking for the simple query.

 

Now, my question is, I have to search single record in a table that has one million rows. So, if I search one record in that table using one user_id, how much time will it take with mysql.

 

The query that I am executing is : select * from people_record where user_id=AAAA_00001

 

 

Will this query provide the output fast or I have to use some searching and sorting techniques to sort the database. 

[dalecosp]

Well, Pawan, you're "hijacking" by attaching your question to another thread, but I'll try and shed some light anyway.

 

select * from people_record where user_id=AAAA_00001

A couple of observations about the code first:

 

1] Do you really need to "select *"? It's better to select only the data you need unless you truly need it all.

2] You must use quotes around the user_id comparator unless it's completely numeric: "where user_id='AAAA_00001'" ...

 

That said, MySQL should quickly retrieve the correct row ... IF the row is indexed it will help. Is "user_id" a primary key (it usually is).

 

Here's an example of a table where the ID field is the primary key:

mysql> describe companies;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| name          | varchar(256) | YES  |     | NULL    |                |
| url           | varchar(50)  | YES  |     | NULL    |                |
| business_type | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

MySQL is indexing the "id" field, so queries using this field as a selector will be faster than queries using the "name" field as the selector.

 

HTH,

Edited November 14, 2013 by dalecosp