MYSQL best practices

[rica-carv]

First, sorry for asking this in here, but somehow is related to e107....

I have a plugin that deals with a huge amount of data (right now his db has 10mb...), and since i'm almost a newbie with MySQL, i would like your opinions regarding data gathering. For instance, is better (regarding performance and database stress) to have a query that gathers all the data, or only the essencial data and build after querys to pick the specific data?

Explanation: I have a HTML table that shows 5 fields from the data, but when hovering over the row, it shows the remaining data in a popup modal.
So, is better to have a single query to fetch all the data at first, or a query for the 5 fields and after a query to fetch the specific data for each row?

What's your thoughts about it? What should be the better way?

Thanks

[Vodhin]

If data is only needed when a user interacts with the page, then it is probably better to use AJAX to fetch what is needed when it is needed. Assuming that this user is one of many (a visitor or member) viewing a public area of the website, then consider how that interaction behaves: A mouseenter/mouseleave event should have the data there, ready to show because it may not be an intentional interaction. A click event should use AJAX since the user definitely wants that data. If you are coding an Admin area then I like to fetch as much data as I can and dump it into an Object that jQuery can deliver through grep or find functions.

With that said, the amount of additional data you want to have should be a factor for which event type you use for the interaction. I would only use a mouseenter/leave to show a small amount of data and a click/tap event for larger data.

But, honestly, I am not an expert on making streamlined plugins: I'll add you to my Real Estate Plugin repository so you can see what a mess I get myself into during development. Look for an invite and download it if you want to play - I think it is stable...

[Jimmi08]

I asked a similar question years ago. After time I stopped to ask. Your help with traits - if you remember - was the latest helpful answer.

I started to use e107::getDb()->retrieve() whenever I can (depending on record number). With custom queries too (like "SELECT... ") too.
Reason? It saves data to an array and you don't need to worry about one query inside other (or getDB(2)) - or something like that, there is an issue with the last called query (or was, I lost track of it).

I often use object/class static variables for data if they are not changed, not displayed. In construct method so I don't need to worry with init(). Then I work with this variable. e107 is singleton, so the class is called just one time... (this approach is used for book/chapters too)

Lately, I am using e_event widely for data manipulation on frontend or in custom scripts. To not repeat stuff and don't create new classes...

If you want the right answer to your question, set DB queries debug mode ON and count queries time. You will see then - because result depends on your table setting, indexes etc. And while on it - you could compare retrieve() with manual written query and e107 way with parameters.

[rica-carv]

Mainly i was asking this because som server limit the MySQL queries, and to add injury to the pain, bootstrap requires sometimes all the data to be available in HTML to use popups....

[Vodhin]

... some server limit the MySQL queries...
Seems that e107 runs about 17 queries all by itself (checked a page in my plugin that doesn't use any query);

...bootstrap requires sometimes all the data to be available in HTML to use popups....
Huh... I didn't know that, and I don't plan on appeasing that demon (I'll code my own pop-ups, thankyouverymuch)

[rica-carv]

Seems that e107 runs about 17 queries all by itself (checked a page in my plugin that doesn't use any query);

E107 is easy on MySQL, but i have a plugin that goes heavy in DB. It started with 200 queries per page (and i was and i'm still limiting views, making pages), and now it has 20/30....
So that's the purpose of my question...
I'm not worried with E107, i want is my plugin faster and not so "hungry" on the DB...

Huh... I didn't know that, and I don't plan on appeasing that demon (I'll code my own pop-ups, thankyouverymuch)

Imagine someone creates a template for your plugin, and decides to use bootstrap....
Of course, if your plugin is private, you don't have that issue...