stubarny

Do you mean select all 600,000 rows of data and then loop through every Nth row of the results array? Is that not very computer intensive? (I expect the table to have tens of millions of rows eventually)

Please could you tell me if any of these suggestions could work efficiently on a large table?: http://www.orafaq.com/faq/can_one_retrieve_only_the_nth_row_from_a_table Thanks, Stu :confused:

Hi CPD, I'm trying to create a site directory (e.g. http://www.linkedin.com/directory/people/c.html) So I need to find the first and last webpage name in each range of webpages. How can I use the WHERE statement to find the Nth record in an indexed field? Thanks, Stu

Hello, I run the following query 80 times in a loop: The table has about 600,000 records. If the values used for $sql_query_line_item_number are low then it takes about 4 seconds to run all 80 queries. If the values used for $sql_query_line_item_number are high then it can take about 60-90 seconds to run all 80 queries (i.e. about 1 second per query). Is there a way to massively speed this up when large values are used for $sql_query_line_item_number? (I have a unique index set up on field 'website_page_name' so I thought this would have made it fast?) Many thanks, Stu

Hello, The rewrite rule below works except if I have no characters infront of the word "jobs". Therefore "www.test.com/test-jobs-in-london" works but "www.test.com/jobs-in-london" fails (i.e. "webpage can not be found). But I thought that "*" meant 'zero or more than zero' so I don't understand why "www.test.com/jobs-in-london" is failing? May thanks for any help you can give, Stu

Hello, Please could you tell me how to change the query below so that it only gets inserted into the mysql table if the value for the field "website_page_name" is unique? (I already have the field setup as unique in mysql but I think I need to change this insert query as well?) Thank you for your help, Stu

Hello, I have the following sql query which joins two tables. To speed things up I'd like to set up an index made up of all the columns that have conditions specified in the query: research_job_searches_results.research_job_searches_results_company_location_description research_job_searches_results.research_job_searches_results_index geocodes.geocode_latitude geocodes.geocode_location geocodes.geocode_longitude Please could you tell me if it is possible to create an index that covers 2 tables? Or should I create two indexes, i.e. one for each table? ... i.e. one index for: research_job_searches_results.research_job_searches_results_company_location_description research_job_searches_results.research_job_searches_results_index and another index for... geocodes.geocode_latitude geocodes.geocode_location geocodes.geocode_longitude Many thanks, Stu

Thank you, I will rework another solution.

Hello, I have the following code which is running very slowly (about 6 full seconds). Currently it is joining on millions of rows and then limiting to 100 rows at the end which seems a waste of resources? Please could you show me how rewrite the query so that does it starts with the "ORDER BY" and "DESC LIMIT" operations and only then does the LEFT OUTER JOIN operation. Many thanks for your help, Stu

Hello, I have a long list of webpage addresses like this which extends below the fold / the bottom of a user's screen: Webpage 1 Webpage 2 Webpage 3 Webpage 4 Webpage 5 Webpage 6 Webpage 7 Webpage 8 Webpage 9 Webpage 10 Webpage 11 etc... I want to take this long list of words and split them into columns so that the reader never has to scroll down. Please can you tell me the best way of going about this? (should I just design for a specific screen resolution? - if so which screen resolution should I design for?). Many thanks, Stu

Thanks guys, much appreciated. Many thanks for that - I'll keep the design as it is. All the best, Stu

Thanks guys. OK I perhaps i need to rethink my database design if this is going to get slow over time. I have a database made up of website addresses and company names. Everytime a user submits a company name in a form they are also asked to submit the website address. (this is to make sure that the websites are automatically kept up to date). So whenever I need to find the latest website address for a company I just refer to the match with the latest timestamp. Please could you tell me the best way of going about this? How about using a cronjob to delete the duplicates (but I don't like deleting data in case of errors)? Or is there something like a 'DISTINCT' index type in MYSQL? Thanks for your help, Stu

Thanks guys, Just to clarify - I only want to return the Nth record (only one record) but I want to operate the LIMIT function by counting only unique rows (therefore completely ignoring duplicates). Therefore if I had a list of colours below the 3rd unique row is "pink". blue green green pink you maybe want to GROUP BY and select those elements with count() = 1 and then LIMIT your results thanks mikosiko - please could you tell me if this would work quickly for a large database (>10 million records)?

Hello, Please could you show me how to find the 7th unqiue website_page_name from the table website_page_names? The code below finds the 8th website_page_name but includes duplicates, I'd like to only count through unique website page names - please could you point me in the right direction? $query ="SELECT website_page_name FROM website_page_names ORDER BY website_page_name ASC LIMIT 7, 1"; Many Thanks, Stu

Thanks fenway - I've just created an individual index for every column mentioned and it's sped things up nicely! LOL - the column names combine the table name and the 'variable name' e.g. research_job_searches_results_company_name is a column within table "research_job_searches_results" and the 'variable name' is "company_name" - it just makes it easier for me to know at a glance which table each variable orginates from. In hindsight maybe I should have used a seperating character e.g. "research_job_searches_results_-_company_name" I can't remember! - are you thinking I should be using INNER JOIN?

Hello, Please could you tell me how to identify indexes that I need to setup when using JOINS as below? Do I just need to create an index for each column name mentioned below or should I be somehow joining some columns within the same index? Many thanks, Stu $query ="SELECT * FROM research_job_searches_results LEFT OUTER JOIN recruitment_situations ON recruitment_situations.recruitment_situation_index = research_job_searches_results.research_job_searches_results_recruitment_situation_index LEFT OUTER JOIN company_website_addresses ON research_job_searches_results.research_job_searches_results_company_name = company_website_addresses.company_website_address_company_name LEFT OUTER JOIN research_job_searches ON research_job_searches.research_job_search_index = research_job_searches_results.research_job_searches_results_research_job_search_index WHERE (research_job_searches_results_index_of_original_result = 0 AND company_website_addresses.company_website_address_status = 'new') ORDER BY research_job_searches_results_index DESC LIMIT 100";

out of curiosity - if we use a calculated column (@counter := @counter + 1) is this going to become slow for larger databases (e.g. 10 million records)?

Thanks Fenway, Is the @counter expression part of the php code like below or is it embedded somehow into the SQL query? Set @counter = -1; $query ="SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING ((@counter % 120 = 0) OR (@counter % 120 = 119))"; $result=mysql_query($query) or die ("Query failed."); Thanks, Stu

Thanks guys, Please could you tell me if I can nest arguments in a HAVING clause like this?: Set @counter = -1; SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING ((@counter % 120 = 0) OR (@counter % 120 = 119)) Many thanks, Stu

LOL let me have a try... Set @counter = -1; SELECT customer_index, customer_name, customer_timestamp, @counter := @counter + 1 FROM Customers HAVING @counter % 120 = 0 Am I close? :-) Stu p.s. please could you tell me what the "%" in "% 120 = 0" does?

Thanks guys, much appreciated. "I'm getting to this rather late -- but why can't you simply enumerate the rows and use HAVING to pull out the ones mod 120? Yes, the "end" is a boundary case, but it's easy to find the "last" one." Please may I ask how I would go about doing this? (sorry I have googled it but I can't get my head around it) Thanks for your help, Stu

Hello, I have a database of customers with the following structure: customer_index, customer_name, customer_timestamp Please note the values in field "customer_index" are not incremental. I need to create a webpage with a directory of customer names. This will consist of 120 "name-ranges" e.g: Ace, Peter - Beta, Sam Brains, Ian - Butts, Ryan Cripps, Pete - Custard, Brian etc.. To do this I will count the number of customer_index's using sql, then order the database alphabetically using "customer_name" and find every Nth record (the total number of records divided by 120). Please can you tell me how to do this efficiently - my first thought is to create 240 sql statements (using LIMIT) that find the first and last record of each of the 120 name-ranges but is there a simpler way of doing this (e.g. by using 1 sql statement?) Thanks, Stu

Thanks little Guy, I think I've got to the bottom of it. I was putting the following preg_match within the loop. The outputted $result variable from the preg_match was messing up the msql_fetch_array - can't believe that took me 2 days to figure out! Thanks for your help though - I'll use the while loop in future, it reads more cleanly. Thanks, Stu if (preg_match("/$needle/i", $haystack, $result)) { # echo "<br>A match was found.<br>"; # echo "<br>0, $result[0]<br>"; # echo "<br>1, $result[1]<br>"; # echo "<br>2, $result[2]<br>"; $display_research_job_search_url_keyword = $research_job_search_url_keyword; $display_research_job_search_index = $research_job_search_index; }

Hi Little Guy, Well I'm confused, the solution in the 2nd post I put up has stopped working. Then I tried yours and it's working perfectly. Please could you tell me what I did wrong? Thanks, Stu

just re-created this from different set of source code and it seems to work. I'm not sure what i've done differently but here's the solution: $query ="SELECT * FROM research_job_searches"; $result=mysql_query($query) or die ("Connection to database table failed. 106." . mysql_error() ); $nrows=mysql_num_rows($result); if ($nrows<>'0') { for ($i=0;$i<$nrows;$i++) { $record = mysql_fetch_array($result, MYSQL_ASSOC); } }