Jump to content
Larry Ullman's Book Forums

How To Make One Database Query From Two?


Recommended Posts

This does not fit exactly with this book but I am not sure where to post this. I have a situation where I am trying to reduce database calls and am trying to come up with a possible solution.

 

This is an example from a CMS I am putting together which I am updating based in part from the site one example.

 

 

I came up with an idea of how to go about it but wanted input to see if others had better way.

 

 

Query:

 

SELECT * FROM posts WHERE posts.status='publish' AND posts.level='".$access_level."' ORDER BY id LIMIT $pageNum, $totalRows

 

 

This is a pagination call to the database to retrieve X rows per page, so each query only has X posts returned.

A new call is queried on each page and then X more posts are retrieved based on WHERE filters and then I have

further filters in code.

 

I have a top 5 recent posts box, based on filters via IF statements, that display post titles only.

 

It is possible to limit posts retrieved per page to any number.

Lets say there are 50 total posts in the database and only 5 posts shown per page.

This makes 10 pages and 10 separate calls to display all records, 5 at a time.

 

I want to limit MySQL calls and wondered if there is a way to do the following in one call.

 

 

Problem:

--------------

 

50 total records.

 

5 records per page.

 

10 total pages.

 

Each call results in only 5 records being pulled from database.

 

Top 5 recent posts displayed by title, filtered by access_level ordered by record id in descending order.

 

It is possible that less than 5 records matching filters for top 5 recent posts, will not be return in ONE call.

 

Example:

-----------

Possible 2 on page 1, another 1 on page 4 and remaining two posts on pages 7 and 9,

making it total of 5 top recent posts filtered by access_level and several other factors not mentioned.

 

To reduce MySQL calls I use the result from the initial page query and see if any matching records are inside.

If so then it adds the title of the post to the Recent Posts box on the page.

 

I am using a WHERE clause for this, resetting the seek of the mysqli result array to the 0 element and setting two counters.

One counter to count the total matching rows so it knows to stop when it reaches 5 matching rows

and a second counter to check total rows and compare to current loop iteration and break out of the WHILE loop

or else a never ending loop would happen if there are indeed less than 5 matching rows found.

 

 

Question:

-------------

 

Is there a way to get TOTAL records in the database from the single query above using MYSQLI functions or something?

Right now I only get the total records returned by the current page, which in this example is 5 records NOT 50.

 

Right now if there are no matching records or less than 5 in the first return result then I need to make a second call

to the database to bring out all records. It would be simpler to setup a dedicated query just for this and let mysql

handle this for me but I wanted to see if there was a way to do it in one call not two seperate ones.

 

The best solution I can come up with is to check the current returned result from the first query for the page above,

then if there are not a total of 5 matching results THEN do a second call via a dedicated query for this Top 5 box.

I would need to compare and filter out results that matched, if any, from the first query or else I would possibly

only have 5 total results but with 2 or more duplicates.

 

This is sort of a complex problem but minor compared to scope of project.

 

I need to know if it is possible to get total records in database from a LIMITed based query

like the one above.

 

The more I look at this the last solution I came up with might be the only way, is there better way?

I am trying to limit as much database querying as possible.

 

 

Thanks.

 

 

 

I am using mysqli and currently my testing environment is set to php version 5.3.6 though I alternate between 5.2.17, 5.3.4 and 5.3.6 via a custom software I created that changes the versions for me on the fly.

Link to comment
Share on other sites

Mysqli have functions called mysqli_multi_query mysqli_more_results: http://php.net/manua...ore-results.php

 

$query  = "SELECT count(*) as count FROM posts";
$query .= "SELECT * FROM posts WHERE posts.status='publish' AND posts.level='".$access_level."' ORDER BY id LIMIT $pageNum, $totalRows";

 

More than that, I cannot help you really. I haven't played with these functions yet.

 

The thought is to use the first query to store the number of rows in the database, and the second one to get results.

 

Hope this help, with the thought process at least. :)

  • Upvote 1
Link to comment
Share on other sites

Terry, I'll be honest, I'm a little confused about what you are asking, but here's how I'm interpreting your post:

 

You have a bunch of data in a database that you want to paginate, but instead of having to make a new database call everytime you go to another page, you want to know if it's possible to make one database call at the beginning to get all the data, and then still achieve pagination. Is that correct?

 

Well, I would appreciate some clarification on your part, but assuming that that is what you're asking, you can use Ajax to accomplish that. I think there are a variety of approaches, but what I'm thinking is that you make an Ajax query on page load, store all that data in an array of objects (as an example), and then limit the amount of data displayed on the screen via JavaScript. Since all your data would be stored in one JS variable (the array of objects), you could simply set up events to display different parts of the array data when another page link is clicked, etc.

 

Anyway, sorry if I'm way off on what you're asking, but please let us know.

  • Upvote 1
Link to comment
Share on other sites

You have a bunch of data in a database that you want to paginate, but instead of having to make a new database call everytime you go to another page, you want to know if it's possible to make one database call at the beginning to get all the data, and then still achieve pagination. Is that correct?

 

No.

 

 

On right side of page there is list of blog posts, split up by X number of pages in pagination.

 

This SQL call handles that.

 

SELECT * FROM posts WHERE posts.status='publish' AND posts.level='".$access_level."' ORDER BY id LIMIT $pageNum, $totalRows

 

On left side in a smaller sidebar is a box that contains the last 5 most recent posts filtered by special filters and it is possible that not all of the 5 most recent posts will be contained within one page. Depending on how many pages, in my example above there are 10 pages with 5 posts on each page, so there could be 1 matching post on page 2, another on page 4 etc...

 

 

2 separate calls, required. Two separate display filters for same content contained in database.

 

I am using the call above, that the original purpose is for pagination to also check if there are any matching records for the top 5 box.

 

Problem is, it only works for the page in the blog post listing you happen to be on.

 

I am trying to limit database calls and merge calls as much as possible.

 

In this case, what I am thinking that will work is to use the first call to check if there are any matching records for the recent posts box and if there is not a total of 5 records found on that particular page to then make a separate call specifically for this 5 posts box, which is separate call than the one above, that SQL call is not listed here due to not being written yet.

 

With the call I show above, there is no way to get a TOTAL of all records in the database, it only shows in this case the last 5 records in a descending order, last to first.

 

The 5 recent posts box is NOT just a recent posts listing, or there would not be an issue. It is a list of recent posts of certain access levels, and 2 other filters. It is not just the last 5 records in the database.

 

My questions are:

 

1: How can I get TOTAL records in the database from the call I show above?

Antonio, showed a method, I will try it out when I start working again.

 

 

2: Is it possible to reduce these database calls into ONE call?

 

I am not sure about this. I am checking the pagination call to see if there are any matching top 5 records and this could save on the second call needed OR reduce the amount of records required to filter in the second call. The second call is not written yet and would be a separate SQL to the one listed above.

 

 

3: How can this process be further optimized?

 

This is a brain teaser problem. Can this process be optimized further?

 

 

You do NOT want to retrieve all records and rows into an Array and then just filter through the array. You will end up crashing the server if there is a lot of traffic and/or a lot of records. The useable memory will get used up fairly quickly.

 

Lets say you had 1200 records and you are doing 30 filters with the data on page one. You have 2500 unique visitors a day each one retrieving all 1200 records to do 30 filters on page one... At the very least your site will crawl to a halt and your site would disappear from the search engines rankings, due to not loading. Getting all records and performing calculations, filters etc.. is a very bad idea unless you have a very small amount of records at all times.

 

I tried to detail the exact process in my first post, step by step what it is doing. I hope this post clarifies further.

 

Thanks.

Link to comment
Share on other sites

Yes, I better understand what you're asking for now. Thank you for explaining that.

 

I suppose the best answer to your question would be to try and reduce the filters and conditions you are using to limit the data. I feel like two relatively unrestricted database calls will be less of a load on your server than one call that has a ton of limiting factors built in (although I'm not sure about that).

 

Either way, I think there are a couple of things you can do. I don't know exactly how you're handling the different permission levels, but maybe you could always return the 20 most recent records, as you'll be guaranteed to have 5 within those 20 that match your filters. From there, you could use JavaScript to filter the results. Because JavaScript runs on the client side, not the server side, server resources would not be taken up filtering the results. Naturally, that would require Ajax, but it's a viable option, I think.

 

Similar to the above, if you could use a database call to determine where the 5th record that meets your conditions is, you could simply retrieve all the records to that point (which shouldn't be too many), and then further limit them as necessary. Again, I'm not exactly sure what you want to do here.

 

On a side note, I recently read an article about Facebook's technique for loading pages so quickly, and it involves using a custom object called BigPipe to make multiple asynchronous Ajax calls to a database, so that as each call is returned, it can load a small part of the page without having to wait for the rest of the database calls to finish. Likewise, you could set up two different Ajax calls to your database, and just load each one as they are returned.

 

Any thoughts, opinions or objections would be appreciated. Thanks.

Link to comment
Share on other sites

I feel like two relatively unrestricted database calls will be less of a load on your server than one call that has a ton of limiting factors built in (although I'm not sure about that).

 

I don't know either, I never tested it to see. I would assume that indexes would save on processing power as well. Also caching results, either through MySQL or outputting static files that are updated once content is added or changed.

 

 

maybe you could always return the 20 most recent records, as you'll be guaranteed to have 5 within those 20 that match your filters.

 

I can't know this. What if there are 40 posts for level 1 before one post is added at a higher level or 60 posts before one post at level 3 for instance. I don't know where they will be in the database, it is not a preset thing.

 

 

if you could use a database call to determine where the 5th record that meets your conditions is, you could simply retrieve all the records to that point

 

That would be what the SQL would do for the 5 top results box, but would count upto 5 then stop. Essentially grab the first five that match the criteria. I have never written SQL that starts at the last number before, Say there are 15 matches in the database you would say start at number 5 and work back to number 1. I am not sure that is how it works, I think what happens is the database gets all the records upto a given number that match then sorts by order and ascending or descending depending if you sorted the output or not.

 

 

 

On a side note, I recently read an article about Facebook's technique for loading pages so quickly, and it involves using a custom object called BigPipe to make multiple asynchronous Ajax calls to a database, so that as each call is returned, it can load a small part of the page without having to wait for the rest of the database calls to finish.

 

I have heard of this tactic before. I do not know how to do it. I also do not know much about ajax, I did purchase Larry's book on building a website with ajax and I also found a couple ajax books I had here but I have not gone through them yet.

 

I think that my idea of checking if any matching rows are within the current page and then search for additional records as needed through a second SQL query is the only option unless I just go straight to the second call from the beginning. I don't know that much about prepared statements so I don't know if those would work here or not.

 

I think I may end up creating a cache via static files that are created from database output and then just update those pages as content changes. Some pages it is impossible to do this, such as a search page but if most of the pages are static it won't really matter I guess.

I will look more into that later.

 

Thank you for your responses.

Link to comment
Share on other sites

Thanks for your response. I definitely recommend learning more about Ajax. While PHP will always be a solid backbone for sites, more and more, JavaScript is taking center stage these days.

 

If I were you, I'd probably just make two database queries, and test it out like that for a while, seeing how much of a load it actually makes on your server. If the load is unacceptable, then you can start considering alternatives.

 

It just crossed my mind that maybe you could use an OR statement in your query. For example, WHERE something OR something-else. Of course, that might cause some overlap issues.

 

The more I think about this, if the query gets too difficult, making two simple queries will work better.

 

This is quite often the case with regexes as well. Quite often, two simple regexes are better and more efficient than one complicated regex that accomplishes the same thing.

Link to comment
Share on other sites

I generally think that trying to limit the number of database queries is a good thing, within reason. My rule would be to cut out unnecessary queries, and I don't think you have any unnecessary queries here. Moreover, you have two fairly straightforward queries. To combine these into one complex query, as HartleySan already suggested, might be a net negative, because of the extra logic and effort required. Using caching for the queries you do have is always a good solution, though, especially on busier sites.

Link to comment
Share on other sites

 Share

×
×
  • Create New...