Keeping a text field in separate table...

[pastvne]

Hi All:
Assume I have a list with a text field that I convert to medium text at the MySQL level
Assume I have 750,000 rows in my database
Assume that when viewing the list, that I don't need the data in the medium text field
Assume that when viewing the list, that I don't need to 'search' based on content in my medium text field.

Now then, given these assumptions, would it be significantly faster when viewing the list if I create a 'viewable' list without the join of the medium text field then use a custom URL to the list with the join to the medium list when (and only when) I need to interact with the data in the medium text column?

I'm trying to come up with a way to get better performance for my largish table. In my other thread, I'm looking at MySQL partitioning. In this one I'm proposing to move the non-critical medium text into another table to try to improve the list view performance of the first list (without the join of the medium text).

I realize that Hugh and Rob are on project right now but I'm open to any suggestions as I'm getting to the point where I need to try something but I don't have a lot of time to waste on invalid ideas either.

Any advice from anyone would be appreciated.

Regards,

Bruce Decker

 

[troester]

Did you try to set your element's list view settings to "include in list query" = no?
This way it shouldn't be called in list view but still there in forn/details view.

Do you really have a join to your text field?
I think joins will slow down a list view.

 

[pastvne]

Hi Troester:
Thanks gain for the tips. I tried setting the param to include list in query =no. I re-ran some benchmarks but I didn't see any noticeable difference in speed. I checked the fabrikdebug=1 setting to verify that indeed the target field was being excluded when the setting was 'no and included when 'yes'.

Also, thanks for the input regarding joining of the text field. I'm trying to thing through all options to speed up my app. Right now, MySQL partitioning seems to hold the most promise but I suspect there may be a fundamental incompatibility since, I think that F! required PKs and MySQL Partitioning seems to want these disabled.

I'll do more reading. For now, just wanted the say thanks for the insight into that param and provide the result of my test.

Thanks again!

 

[troester]

Now then, given these assumptions, would it be significantly faster when viewing the list if I create a 'viewable' list without the join of the medium text field then use a custom URL to the list with the join to the medium list when (and only when) I need to interact with the data in the medium text column?

Yes, this is what I would try, too (I'm really no tuning specialist).
One Fabrik list without join, only with elements needed in list view
and a 2nd "complete" one called with custom URL to show form/details.

I don't know your setup. Maybe you could use a MySQL view (including some prefiltering on your 750'000 records ) to link the "list" list to.

 

[pastvne]

Thanks. I'll add this to my list of experiments. I'm reading a lot about MySQL partitioning and it seems that it was designed precisely to overcome the scalability issue I'm facing. But I'm still putting my head around it's requirements regarding primary keys and how that will fit within the Fabrik way.

Will keep you posted and thanks again.
-BD

 

[cheesegrits]

I'm intrigued as to why, if it's the medium text slowing things down, exluding it from the list query would have no effect on performance. Suggests that the bottleneck may be elsewhere.

Are you familiar with MySQL's "explain"? When faced with issues like this, I usually copy the query our getData() is using, and "explain" it in phpMyAdmin.

-- hugh

 

[pastvne]

Thanks for the reply Hugh. I had initially tried explain and looked specifically at the indexes the query was using but I didn't spot anything unindexed. Then I proceeded on the theory about Mediumtext since all other fields are of smaller types. In my test, I tried disabling the mediumtext element and tried leaving it on but checking the elements properties to remove it from query. I checked the getData query to ensure it had indeed been removed.

So, I suspect you are correct that the bottleneck may be elsewhere.

I'm on a server that otherwise seems to be pretty snappy. Other Joomla tables process quickly but I must admit that they aren't heavily populated so that's not such a good test.

Now, when I say performance issue, I'm talking about 10-15 seconds for 250K records in the database returning about 1800 in the result set. I've removed ordering from the list and all WHERE elements are indexed. Am I whining about something that seems to be in the ballpark according to your experience?

I work with other databases that would't blink at 250K records even unindexed. I want to first check that my goal/expecatation isn't unreasonable.

This database stores cronological records. In my test example, these are customer invoices and statements that are temporal. It's important that 'recent' documents (say last 90 days) can be accessed quickly. But I'm perfectly okay with having to wait up to 30 seconds to retrieve older archived record sets. This is why I've been studying MySQL Partitioning.

My idea is to create a partition for each month of the year (that should be sufficiently granular) and then apply a URL filter to a list to grab only the last 90 days of records. I guess a question would be whether a URL-based filter would be applied early enough in the chain of queries to exclude records outside of 90 days using partitioning. Perhaps I'd have to create a copy of a list and apply pre-filters to ensure that Fabrik would not query partitions outside of the 90 days?

I'm I thinking wrongly or jumping into partitioning too early?

I guess I'll re-run the explain and share it here to see if you can help me spot anything glaring. Also, if you would offer me you opinon about whether you'd expect on a reasonably fast SSD, 20+GB RAM server to be able to see queries against a 250K record table come in more quickly, that would help me decide if partitioning will hold much promise.

Lastly, I was hoping that if any of the F! developers out there have used MySQL Paritioning? If there is something fundamentally incompatible between Fabrik and partitioning, then I can avoid that whole expedition.

Thanks again for your time and assistance.

Regards,

Bruce Decker