Best database (mysql) structure for this case:

Question

we have three types of data (tables):

• Book (id,name,author...) ( about 3 million of rows)
• Category (id,name) ( about 2000 rows)
• Location (id,name) ( about 10000 rows)

A Book must have at least 1 type of Category (up to 3) AND a Book must have only one Location.

I need to correlate this data to get this query faster:

Select Books where Category = 'cat_id' AND Location = 'loc_id'

Select Books where match(name) against ('name of book') AND Location = 'loc_id'

Please I need some help. Thanks

Answer 1

With tables this size, which I would consider medium-sized (not small, not large), it is more likely the indices which will make the difference. Create the tables correctly and join them appropriately (on the indexed primary and foreign keys) and your performance should be fine.

Answer 2

Have another table, say bookscategories, which has 'id , bookid, categoryid' as fields.

Use this to map books to categories.

Both your original queries will not be affected since the first query wants books in ONE specific category and location and the second query wants books that match a title and ONE location.