About Speed performance - Support Forums

This topic has 3 replies, 2 voices, and was last updated 2 years, 5 months ago by Anh Tran.

Creator

Topic
July 3, 2019 at 6:25 AM #15214

Resolved clientes@interficto.com
Participant

Hello, I am developing a special project. It will have 2 "Custom Post types" and each one will have near to 30 Custom Fields (For example price, Zona, weight ) my main worried is:

I plan have near to 2.000 post en each CPT i mean 4.000 posts in total, so I will have 120.000 records in the meta table.

I will have make search by each of 30 Custom fields. In your experience. The seach result will fast? Can you tell me some recomendations for improve my work. May be can I get a best performanc e using "Custom Table" Addon?
Creator

Topic

Viewing 3 replies - 1 through 3 (of 3 total)

Author

Replies
July 3, 2019 at 9:20 AM #15217

Anh Tran
Keymaster

Hi Clientes,

This is an interesting question. Performance is a big problem if you make query by custom fields, and it's not recommended to do that. See this post for details:

https://metabox.io/custom-fields-vs-custom-taxonomies/

Unless you use a plugin like SearchWP, which will index your content and perform an optimized search, it's not recommended to store large data in custom fields.

In my opinion, the best way is using custom tables (with MB Custom Table extension). It helps organizing data and making SQL queries faster. However, you have to write the queries yourself.

See this post for more details:

https://metabox.io/optimizing-database-custom-fields/

July 8, 2019 at 11:13 AM #15284
clientes@interficto.com
Participant
Hello Anh, thank you very much again. I only have one question more.

Finally I will use custom tables, however in the documentation I found the last example to search in the custom tables:
```
global $wpdb;
$ids = $wpdb->get_col( "SELECT ID FROM your_table WHERE field1 = 'value1' OR field2 = 'value2'" );

$query = new WP_Query( [
    'post_type' => 'post',
    'post__in'  => $id,
] );
```
So you first I should make a query to the custom tables and next you make a second query to wordpress post table passing the ID from first query.

If for example the first query get 5000 records so you pass 5000 ids for the second query?

Do you think taht it is most optimize? I am scare about it and I don't know if is a good idea use metabox to work with big data in database.

Can you recomend me something?
When I use it:
```
$query = new WP_Query( [
    'post_type' => 'post',
    'post__in'  => $id,
] );
```
I can't imagine one query passing 5000 records the SQL query could be:
where ID = 'ID_1' OR ID = 'ID_2' OR ID = 'ID_3' ....... ID = 'ID_5000'
July 8, 2019 at 1:55 PM #15285
Anh Tran
Keymaster
Hi,

Passing IDs directly in the post__in parameter will generate a SQL query like this:
```
SELECT * FROM wp_posts WHERE ID IN (1,2,3)
```
It doesn't generate OR statement as you thought.

Because the ID is the primary key of the posts table, this query is performant and faster than normal queries.

If you're worry about getting 5000 records from the DB, then imaging this technique as breaking a very slow query (if you do the normal way with WP_Query and JOIN) into 2 fast SQL queries. Although the number of queries increases, the performance is much better.
Author

Replies

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.