manhattes Posted July 29, 2015 Share Posted July 29, 2015 I have the following code which works fine with the exception of timing out because there is a lot of data to filter through. Is there a way to create a new table either from the PHP script or from within SQL to stop it from timing out? $query = "SELECT s.SName, s.Symbol, c.`CD`, c.`FR`, c.`PCD` FROM Stocks s LEFT JOIN Calendar as c ON c.SName LIKE CONCAT('%', s.SName, '%' )LIMIT 200"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)){ Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted July 29, 2015 Share Posted July 29, 2015 What do you mean "a lot of data". MySQL and PHP are pretty efficient when handling lots of data. If you are getting a timeout then there is most likely something in your code which is slowing it to down, worst case being an infinite loop. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 29, 2015 Share Posted July 29, 2015 Does the SName start with the value you are trying to match? If so, use CONCAT(s.SName, '%'). Do you have an index on c.SName? Have you tried running the query on its own with Workbench or phpMyAdmin? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 S.Sname only contains what needs to be found within C.SName It can be anywhere in the field of C.SName When I type the query into the SQL line it says: #2013 - Lost connection to MySQL server during query Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 What would I add to the code if i want to place the new results into a new table and move the searched results into a "finished" table? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 Anyone? Beuller, Beuuuullllllerrrrrrr Quote Link to comment Share on other sites More sharing options...
Barand Posted July 29, 2015 Share Posted July 29, 2015 So it's timing out because it is too slow and you now want to make slower by adding inserts? Do you want to show all Stocks or just those that match Calendar? If you only want matching, use INNER join instead of LEFT, it's faster. BTW, how many rows in each of those tables? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 There are about 1000 rows in Stocks and 191,000 rows in calendar. There is more than one result for each stock usually. I was thinking i could just create a new dataset out of it so it would go faster if i want to sort by dates, products, updates. etc.Just trying to figure out a way to see all the matches. If there is an easier way just through script I am open to it. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 29, 2015 Share Posted July 29, 2015 Anyone? Beuller, Beuuuullllllerrrrrrr Patience - we are not being paid by you so are not working on your time. Those are not particularly large tables, and, as Ch0cu3r said, there is probably something wrong somewhere else, perhaps a corrupted table. But you should consider redesigning the database so you are not having to read every record searching part of field. Use whole fields that can be indexed. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted July 29, 2015 Share Posted July 29, 2015 food for the thought: (meaning for you to read and investigate the causes of the error and do not create a worst bandaid) http://dev.mysql.com/doc/refman/5.6/en/gone-away.html http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet and a question: What type/size are your columns called SName (in both tables)? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 Thanks Sen. Very helpful comment. I guess that is a possibility. If that is the case I need to clean the data by going through it a few hundred records at a time. I can apply the index later since it has nothing to do with what I am trying to accomplish. I am creating a brand new dataset that is expedited through the LIKE function since I just need to find a similar phrase/name. I guess preg match might work but i want it to be fuzzier logic. Can you advise what to add to create a new table and delete the records from Calendar as I move through the list? That way if it is a corrupted row I can find it. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 food for the thought: (meaning for you to read and investigate the causes of the error and do not create a worst bandaid) http://dev.mysql.com/doc/refman/5.6/en/gone-away.html http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet and a question: What type/size are your columns called SName (in both tables)? Everything is VARCHAR or DATE Just need to know how to incorporate insert and delete so I can go through it and make a new table. Quote Link to comment Share on other sites More sharing options...
iarp Posted July 29, 2015 Share Posted July 29, 2015 What is in Calendar.SName and Stocks.SName? Is there more than one Stocks.SName in a single Calendar.SName row? Is that why you're having to do the LIKE statement? If that is the case, you would've been better off using a linker table based on the auto_increment id within Calendar and Stocks table. It may not be too bad of an idea to add one now as the LIKE statement is what is most likely causing your timeout. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 What is in Calendar.SName and Stocks.SName? Is there more than one Stocks.SName in a single Calendar.SName row? Is that why you're having to do the LIKE statement? If that is the case, you would've been better off using a linker table based on the auto_increment id within Calendar and Stocks table. It may not be too bad of an idea to add one now as the LIKE statement is what is most likely causing your timeout. The stock sname is just one company per field The calendar SName may contain more than one company per field if say two companies on the stock list are working together on the same thing in real life, however, It should just give a duplicate under each SName which is OK with me. How would the auro_increment ID be helpful if I am trying to create a new dataset that is not based on anything except a match between SName? Quote Link to comment Share on other sites More sharing options...
iarp Posted July 29, 2015 Share Posted July 29, 2015 You should have a column on Calendar and another on Stocks, people typically name them id. In MySQL when you create the table you can set ID INT AUTO_INCREMENT which will automatically assign a new integer value to every single row that is unique to that specific row on that table. If you had a one-to-one match you would have a second column on the Calendar table called stocks_id and what that column would contain is the Stocks.ID column value, so when you go to join the tables in the query you say Calendar.stocks_id = Stocks.ID and there is no searching require by MySQL to return that join. Its fast and simple. You say there can be more than one Stocks in a Calendar.SName. In this scenario you would have a third table that stores both the Stocks and Calendar ids that relate to each other. SELECT s.SName, s.Symbol, c.CD, c.FR, c.PCD FROM Calendar c JOIN Calendar_Stocks cs ON cs.calendar_id = c.id JOIN Stocks s ON s.id = cs.stocks_id WHERE s.SName = 'GOOG' This query is untested, I'm going off memory here and I may have the incorrect join required. But that should return all Calendar items that are linked to a Stocks item with a Stocks.SName equaling a specific value rather than you having the LIKE search for that value. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 You should have a column on Calendar and another on Stocks, people typically name them id. In MySQL when you create the table you can set ID INT AUTO_INCREMENT which will automatically assign a new integer value to every single row that is unique to that specific row on that table. If you had a one-to-one match you would have a second column on the Calendar table called stocks_id and what that column would contain is the Stocks.ID column value, so when you go to join the tables in the query you say Calendar.stocks_id = Stocks.ID and there is no searching require by MySQL to return that join. Its fast and simple. You say there can be more than one Stocks in a Calendar.SName. In this scenario you would have a third table that stores both the Stocks and Calendar ids that relate to each other. SELECT s.SName, s.Symbol, c.CD, c.FR, c.PCD FROM Calendar c JOIN Calendar_Stocks cs ON cs.calendar_id = c.id JOIN Stocks s ON s.id = cs.stocks_id WHERE s.SName = 'GOOG' This query is untested, I'm going off memory here and I may have the incorrect join required. But that should return all Calendar items that are linked to a Stocks item with a Stocks.SName equaling a specific value rather than you having the LIKE search for that value. So using your example setting SName to GOOG, If the contents of calendar.sname is TWitter goog MSFT , Will it consider it a match? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 Well I guess I have to use this topic. Please provide a solution to insert the results into a new table and delete the searched calendar entries. I will do this with LIMITS in place and do it the hard way. This should be easy for a phpfreak :-) Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted July 29, 2015 Share Posted July 29, 2015 Just need to know how to incorporate insert and delete so I can go through it and make a new table. Create a new table. Fetch only the desired results you want. Insert the results from that query or do an Insert/Select in one query. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 Create a new table. Fetch only the desired results you want. Insert the results from that query or do an Insert/Select in one query. Yes but it doesnt complete the task if i do it at once. I have to run the script a thousand times in order to build it properly so I need to remove things that have been matched in the calendar each time the script runs. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 Ive done this before using names to search outside dirty data. Cant find that script though... It was somthing like sql = INSERT..... sql2 = Delete.... Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 i feel so advanced right now, stumping everyone! Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted July 29, 2015 Share Posted July 29, 2015 Not stumping anyone, all the solutions are in the post. If you have a ton of records refresh the script with a header or meta refresh, or set a cron job, limit your results and this will get completed. Don't beat your head against the wall trying to do this all in one go. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 Not stumping anyone, all the solutions are in the post. If you have a ton of records refresh the script with a header or meta refresh, or set a cron job, limit your results and this will get completed. Don't beat your head against the wall trying to do this all in one go. I thought this forum is for coding help not googling help. None of the code posted helps me and I am not trying to do it in one shot, hence my last question how do I incorporate insert and delete into my code to do the matching in chunks. Something like this: while($row = mysql_fetch_assoc($result)){ $sql51="INSERT INTO CombinedList (Rank, NCT Number , Title, SName ) VALUES('$row[Rank]' , '$row[NCT Number]' , '$row[Title]', '$row[SName]')"; $result51 = mysql_query($sql51); echo "Moved " . $row[name] . " from searchable database into revised search table."; echo "<br>"; //and delete search name from main table //$sql52 = "DELETE FROM names WHERE name='$row[name]'"; // $result52 = mysql_query($sql52); Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted July 29, 2015 Share Posted July 29, 2015 If you don't get the answer you were seeking just be more specific as you just did, nobody said to search for anything. Doing it in chunks in a single script is still "all at one time" as far as the php script is concerned. I saw a lot of people trying to help, information and advice "should" be help. When people come here looking for help is no badge next to their names stating their coding levels, at times merely telling them how gets them on the right track....sometimes not. We volunteer our free time here to help others...honestly, not kidding. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 29, 2015 Author Share Posted July 29, 2015 If you don't get the answer you were seeking just be more specific as you just did, nobody said to search for anything. Doing it in chunks in a single script is still "all at one time" as far as the php script is concerned. I saw a lot of people trying to help, information and advice "should" be help. When people come here looking for help is no badge next to their names stating their coding levels, at times merely telling them how gets them on the right track....sometimes not. We volunteer our free time here to help others...honestly, not kidding. I really do appreciate it but I find that people like to comment on how its sloppy code or a different way to reach my objective when they aren't actually listening to what is in the question. I know something like the above code should work but I am not sure about the formatting. I will look somewhere else soon if I dont get an answer soon. Thank you for your help Quick. 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.