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?
The
$wpdbobject 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$wpdbfunctions.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
cityat least). Use theEXPLAINfeature of MySQL to know what the database is doing to execute your query and how you can help it.