Slow SQL query

[stubarny]

Hello,

 

I run the following query 80 times in a loop:

 

$query ="SELECT DISTINCT website_page_name FROM website_page_names ORDER BY website_page_name ASC LIMIT " . $sql_query_line_item_number . ", 1";

 

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

[cpd]

From the look of it your completely miss-using the LIMIT statement.

 

If you want to get a specific website page name you should use a WHERE clause. If you want to select all the website page names and cycle through each one you can use the DISTINCT function and then cycle through your result set....

 

Running the same query 80 times is absolutely obscene!

[stubarny]

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

[stubarny]

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:

[cpd]

If your trying to create a list of something just pull all the relevant data and then manipulate it in PHP.

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

[Jessica]

You could select just the names in order, choose the first, then choose the last. No need for a loop.

[stubarny]

Hi Jesie,

 

Thanks but I need to find the first and last record in 40 ranges, so I'm not sure I can avoid a loop?

 

Stu

[Jessica]

Hi Jesie,

 

Thanks but I need to find the first and last record in 40 ranges, so I'm not sure I can avoid a loop?

 

Stu

$sql = "SELECT name FROM companies ORDER BY name";
$names = array();
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    $names[] = $row['name'];
}
//Splits it into arrays of every 40 names.
$name_groups = array_chunk($names, 40);
foreach($name_groups AS $name_group){
    //Echo the first name, a dash, the last name, then a space pipe space as a separator.
    echo array_shift($name_group).'-'.array_pop($name_group);
    echo ' | ';
}

 

Not tested.

[stubarny]

Oh wow I didn't know php could retrieve to much data so quickly.

 

I added this to the top of your code to stop a memory overload error, and it works in about 6 seconds which is cool.

 

ini_set("memory_limit","999M");

 

Thanks Jesie

[Jessica]

Is there a reason you're unable to spell my name correctly?

 

If it's taking that long, why don't you just do a page per letter or something, instead of splitting it up by groups of X

[fenway]

6 seconds? Memory limit?Something else is wrong.

[TimeBomb]

6 seconds? Memory limit?Something else is wrong.

Well, jesirose's code retrieves all 600,000 rows of data from the table, then spits out all of the rows in chunks of 40. I'm a little surprised this only took 6 seconds, although I assume it's because of the memory limit he put in.

 

Anyways, with a table this big, you should be taking advantage of caching as much as possible, for both MySQL and PHP.

 

To retrieve 40 rows of data from anywhere in the table, use the following query:

SELECT DISTINCT website_page_name FROM website_page_names ORDER BY website_page_name ASC LIMIT 40 OFFSET {row start}

Replace {row start} with the row number to start at. So if the offset is 100 and limit is 40, it will retrieve only rows 100-140.

 

I must still warn you that while offset will help a lot in this scenario, it isn't perfect. When you use offset, it still iterates through all the previous rows, starting at 0; it just doesn't pay any attention to them when showing the result. Therefore, an offset of 500,000 will still be quite slow. I found an informative article about this issue which you may be interested in reading: http://www.4pmp.com/2010/02/scalable-mysql-avoid-offset-for-large-tables/

You may want to look at the design of your entire database if you really want to completely solve this issue.

[fenway]

Yes, if there's something you can "guess at" for the offset (an ID, value, etc.), that helps a great deal.