CODEWITHSUNDEEP
mysqlstructureperformance
robert
robert

Reputation: 25

Best database (mysql) structure for this case:

we have three types of data (tables):

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

Upvotes: 0

Views: 136

Answers (2)

zaf
zaf

Reputation: 23244

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.

Upvotes: 1

MJB
MJB

Reputation: 7686

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.

Upvotes: 0

Related Questions