Fabrik Limitations?

[nycreate]

Fabrik Limitations? Slow search with multiple users on 50k record table

Hi All,

Im working with a 50K+ row table/list (increasing by approx 500-1000 per week). With approx 30 elements per row.
The application, I wouldn't consider advanced, but in F3 and J!2.5.9 its seems much slower and cpu heavier than F2 and J1.5.
Table is currently 30mb.

Site is only 'main' one running on a quad core server (4 x 2.1ghz) with 4 gig ram.
When more than a couple of people start searching the list the mysql cpu usage rockets and simple searches (looking for an order ref or postcode for example) which would normally take max 2 seconds to return a result, can go up to 60 seconds +.

The list has only 10 elements as show in list, of which 6 are searchable.

Is there anything I can do to optomise this? Can I use just a search box on a form to query this list rather than loading the list first?

Thanks in advance.

 

[cheesegrits]

What way are you searching the list? With standard element filters on the list, or via the 'search' plugin?

First thing to check is that all the fields you are searching on have indexes in MySQL. We do attempt to add indexes on any element you enable filtering on, or (I think) include in 'search all'.

But it'd be worth checking the actual underlying indexes in phpMyAdmin (or whatever) and making sure there are actually indexes on everything you search on.

-- hugh

 

[nycreate]

Hi Hugh, Im using the standard element filters.
the indexes on this table contain one for one of the standard element filters, (theres 3 plus an 'any' filter) and none for the others.

 

[nycreate]

Also when editing data, submit can take between 5 and 90 secs. (even if its just changing one field) one example, in firebug, post.html took 15.22 secs and gives '303 see other' error.
The more users and more quickly you try and submit, the slower it gets.

 

[rob]

what element's are you searching on a what set up do those element's have.

Do you have search all on, if so it it advanced serach? Some element's will search better than others, e.g. checkboxes will be slower, as their data is stored as json which we have to query via a regular expression.

Are you searching via the Joomla search plugin (which is slow)?
Are you sure its the filters? Have you tried disabling them and observing the site's performance?

 

[rob]

Have you been able to isolate the queries that slow things down, and then run them through mySQL's EXPLAIN function via something like phpmyadmin?

 

[nycreate]

Hi Rob,
Hugh, is investigating this one for me, He has flicked a few switches that have helped massively already.

Regards.