Can I customised default WP table

Question

I have a XML file that consist of product information.

Format of XML file :

<product>
    <name>Product-1</name>
    <description>this is product 1 description</description>
    <category>Category-1<category>
    <merchant>1234</merchant>
    <price>12</price>
    <instock>1<instock>
    <stockstatus>45</stockstatus>
    <image>http://www.example.com/1.jpg</image>
    <manufacture>987</manufacture>
</product>

I need to write a script that will add these detail as a product. For now I am not using any WP plugin for eCommerce purpose.

I am planning to create a custom post type as wpproduct that will store these products details.

So for now I can use name as post title, description as post content and image as post thumbnail. And for storing the meta data I can store it in postmeta table.

So I can create the post by using wp_insert_post and whatever ID it will return, I will use that ID in update_post_meta to update the meta data such as price, stock status , stock quantity, manufacturer etc.

Like this it can be done..

Main Concern

So for a single product I need to write 1 wp_insert_post and then 6 update_meta_data query. So 7 query for 1 product. Not exactly 7 query as WP inbuilt function uses many query for validation check.

What will happen if my XML file has 1 lakh product. So for adding 1 lakh product 7 lakh mysql query will run.

Assumption

I am just eager to know like can I add custom column in WP wp_posts table so that I can add 1 product in 1 query only. I might use raw mysql query to add it.

Please help me and guide the best way to do it.

Thanks

Answer 1

I've checked and WordPress does, by default, retrieve & cache all post meta along with the main query's posts, so you're perfectly fine storing a product's additional fields as standard post meta.

You might want to store Brands (merchants? manufacturers?) as a custom taxonomy so that you can take advantage of built-in templates and queries to show all products of a certain brand.

Answer 2

Import is inherently heavy operation, because it is bulk operation and writes are inherently very heavy (relative to reads).

If you are not time–constrained for the operation — does it really matter?

1. Write your import as command line tool.
2. Make sure you disable PHP timeout.
3. Make sure you can efficiently restart process at certain point, in case something goes wrong.

After that it takes as long as it takes.

If you are time constrained (for example having to run this often/repeatedly) then you would need to approach this as general performance problem. That is you need to profile actual performance and determine what the bottlenecks are in the process.

In case if multiple queries are an issue you could consider staged process — when meta is first stored as single entry on import and then expanded into more convenient form at a later point.

I would recommend to stay away from modifying native tables since there seems to be little functional need to do so in this case.