creating a new table to avoid timeout

[manhattes]

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)){

[Ch0cu3r]

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.

[Barand]

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?

[manhattes]

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

[manhattes]

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?

[manhattes]

Anyone? Beuller, Beuuuullllllerrrrrrr

[Barand]

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?

[manhattes]

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.

[Barand]

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.

[mikosiko]

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)?

[manhattes]

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.

[manhattes]

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.

[iarp]

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.

[manhattes]

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?

[iarp]

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.

 

[manhattes]

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?

[manhattes]

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 :-) 

[QuickOldCar]

 

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.

[manhattes]

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.

[manhattes]

Ive done this before using names to search outside dirty data.

Cant find that script though... 

It was somthing like sql = INSERT..... sql2 = Delete....

[manhattes]

i feel so advanced right now, stumping everyone! 

[QuickOldCar]

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.

[manhattes]

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);

[QuickOldCar]

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.

[manhattes]

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.