boomarang Posted November 12, 2013 Share Posted November 12, 2013 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, Quote Link to comment Share on other sites More sharing options...
Barand Posted November 12, 2013 Share Posted November 12, 2013 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; } Quote Link to comment Share on other sites More sharing options...
KaiSheng Posted November 14, 2013 Share Posted November 14, 2013 1) Do not use SELECT * FROM wherever in a query. 2) Follow Barand coding ^ Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 14, 2013 Share Posted November 14, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Pawan_Agarwal Posted November 14, 2013 Share Posted November 14, 2013 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. Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 14, 2013 Share Posted November 14, 2013 (edited) 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_00001A 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.