creating a new table to avoid timeout

[QuickOldCar]

Seems your issue is a timeout though. If that's true are trying to do too much work..or have to increase memory or timeout limits on the server, that's if the server will not puke doing the work.

 

That's why I suggested a different approach and baby steps, query just the data you need with limited results and store it, then do the next set.

 

Do you have proper indexing on the columns? , that could speed it up.

[mac_gyver]

to fix your debatable design, you don't need to run the query that doesn't work/times out.

 

you need to select all the rows from the badly designed table, that has the multiple string values stored in the single column, split up those values, get or assign an auto-increment integer id key for each unique string value, then insert row(s), one for each existing row and each original split values, into a new table, with the id corresponding to the string and the id of whatever you are relating that information too.

 

the resulting table should only have integer id values in it.

 

for example, if you have a row in your existing table with only one of these string values stored in the offending column, you would end up inserting one row in the new table. if you have a row in your existing table with three of these string values stored in the single column, you would end up inserting three rows in the new table.

 

once you get finished with this deconstruction/reconstruction process, you would use this new table in JOINed queries to relate, using exact value, integer, matches, the source data in the parent tables. then, by using correct indexes on the tables, you can easily and quickly query tables that contain several millions of rows.

Edited July 29, 2015 by mac_gyver

[manhattes]

I dont know what data i need though, I just know where it might be. I am extracting data from a source that is raw/uncleaned and not meant for this purpose. 

I can re-upload the data  and create an index but I would still need to use the like command or maybe preg_match to compare the two tables and create a table that is by company name with the calendar data.

 

This is what I want it to look like give or take a few fields from the calendar:

AAPL - Apple Inc - Product - Date Release

AAPL - Apple Inc - Product 2- Date Release

AAPL - Apple Inc - Product 3- Date Release

 

MSFT- Microsoft - Product - DateRelease

MSFT- Microsoft - Product2 - DateRelease

MSFT- Microsoft - Product 3- DateRelease

MSFT- Microsoft - Product 4- DateRelease

 

The calendar data looks like this: The Column SName is where it says University of California

Rank NCT Number Title Recruitment Study Results Conditions Interventions SName Gender Age Groups Phases Enrollment Funded Bys Study Types Study Designs Other IDs 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00 Results First Received Acronym 0000-00-00 Outcome Measures URL 129003 NCT00820781 Endoscopic Sclerotherapy and/or Ligation Versus Po... Completed No Results Available Gastric Bleeding|Cirrhosis Procedure: Portacaval shunt|Procedure: Sclerothera... University of California, San Diego Both Child|Adult|Senior   518 Other Interventional Allocation: Randomized|Intervention Model: Paralle... ESTVPCSBGV 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00 No Study Results Posted null 0000-00-00 Survival|Control of bleeding and quality of life  

[Barand]

FYI

I ran a similar query on two of my table using a JOIN ON x LIKE ('%y%') as you were trying

 

Table 1: 9,500 rows

Table 2: 600,000+ rows.

 

5,000 results in 1.1 seconds.