Speeding up viewers
3 posts by 2 authors in: Forums > CMS Builder
Last Post: March 23, 2009 (RSS)
By pothompson - March 23, 2009
I have created a section editor and imported approximately 10,000 records into the MySQL table.
I'm trying to display a number of records from the table using the standard view code (list($records,$meta)=getRecords....) and am narrowing the search down using the where attribute. The where that I'm using is only relates to one column. I was wondering if there was any way of creating an index or similar to speed up this search as currently it seems to take a while.
An example of the full code is
list[/#0000ff]($productsRecords[/#660000], $productsMetaData[/#660000]) = getRecords(array[/#0000ff](
'tableName' [/#008200]=> 'products'[/#008200],
'where' [/#008200]=> $product_where[/#660000],
));
I'm trying to display a number of records from the table using the standard view code (list($records,$meta)=getRecords....) and am narrowing the search down using the where attribute. The where that I'm using is only relates to one column. I was wondering if there was any way of creating an index or similar to speed up this search as currently it seems to take a while.
An example of the full code is
$product_where
[/#660000]='CATEGORY="10" AND CATEGORY="34" AND CATEGORY="78"'[/#008200];list[/#0000ff]($productsRecords[/#660000], $productsMetaData[/#660000]) = getRecords(array[/#0000ff](
'tableName' [/#008200]=> 'products'[/#008200],
'where' [/#008200]=> $product_where[/#660000],
));
Thanks - Paul.
Re: [pothompson] Speeding up viewers
By Dave - March 23, 2009
Hi Paul,
It shouldn't take very long at all with 10,000 records. But it does depend on the web host. How many seconds is it?
You can add the following options to speed things up:
'loadUploads' => false, // won't load uploads
'loadCreatedBy' => false, // won't load values for record author user
And there is no automated way to create an index but you could do that with phpMyAdmin or MySQL Query Browser or something like that.
Also, I'm not sure how this query would return any results because a category couldn't be 10 and 34 and 78 at the same time. Did you mean OR?
Hope that helps!
It shouldn't take very long at all with 10,000 records. But it does depend on the web host. How many seconds is it?
You can add the following options to speed things up:
'loadUploads' => false, // won't load uploads
'loadCreatedBy' => false, // won't load values for record author user
And there is no automated way to create an index but you could do that with phpMyAdmin or MySQL Query Browser or something like that.
Also, I'm not sure how this query would return any results because a category couldn't be 10 and 34 and 78 at the same time. Did you mean OR?
Hope that helps!
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Speeding up viewers
By pothompson - March 23, 2009
I did mean OR, I read and re-read that code before I posted it to the whole world to make sure I hadn't made any mistakes!
Maybe it's not the MySQL taking a long time then, it's just the whole page takes a while to load and I'm trying to replicate an existing (non database-driven) site so I can compare the two and my version is a lot slower.
I'll have a look at a few other areas to see if I can find bottleneck.
Thanks, Paul.
Maybe it's not the MySQL taking a long time then, it's just the whole page takes a while to load and I'm trying to replicate an existing (non database-driven) site so I can compare the two and my version is a lot slower.
I'll have a look at a few other areas to see if I can find bottleneck.
Thanks, Paul.