Jump to content

Mysql Query taking too much time. Almost 10 minutes


boomarang

Recommended Posts

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,

 

Link to comment
Share on other sites

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;
}
Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.