Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I've got a custom database table with lots of data that I'd like to query several times from a custom template file I've built. Say the db table looks like this:

id     name     city          zip
23     Mike     New York      123
54     Peter    Los Angeles   456
78     Steven   Chicago       789
79     Tom      Los Angeles   450

And I want to run at least four queries, looking kinda like this:

SELECT * FROM table LIMIT $paginated,10 (this is presenting all of the names)
SELECT id,city FROM table GROUP BY city (this will be a dropdown list of all the citys)
SELECT zip FROM table WHERE city = "New York" GROUP BY zip

That's only three queries but anyway - is it more effective to just run one big query with all of the data into an array, and then just loop that array whenever needed, or is it better to perform actual db queries?

I'm using $wpdb->get_results for every query of course, is this harder for the server to work with? I mean, using WP code to perform DB queries should result in more PHP code for the compiler to translate, am I wrong?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

The $wpdb object is based on ezSQL, it does not add much overhead, so I would not worry about that. Opening a second connection to the database yourself will probably lead to more overhead than using the $wpdb functions.

The three queries you have described are ideal queries to do in the database (a small result set from a large dataset), so you should not emulate them in PHP. Be sure to add some indexes to the table to speed the queries up (one on city at least). Use the EXPLAIN feature of MySQL to know what the database is doing to execute your query and how you can help it.

share|improve this answer
1  
A further option is to cache result sets with the Transients API. Though the queries are probably simple enough in this case that the difference would be negligible. –  Milo May 30 '11 at 21:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.