CODEWITHSUNDEEP
phpmysqldatabase-design
Jon Skarpeteig
Jon Skarpeteig

Reputation: 4128

mysql select cache optimization

Having a table called 'books', then multiple tables with a primary key, and value: E.G 'library', and 'genre'. Then this primary key is stored in the 'books' table for every book.

What's an efficient way to retrieve all books in library A?

Some approaches:

What's the reccomended (and fast) way of doing this?

Upvotes: 1

Views: 343

Answers (3)

bavo
bavo

Reputation: 507

If you want performance on a relation database you must use Foreign Keys. Indexes are fast but FK's are faster simply because they 'know' what on the other end. If you are still having performance issues after that you must first understand how the DB engine handles your request. If you have a lot of books and only a few libraries make sure the engine selects your library first; not 'books inner join library' but 'library inner join books'. If you don't use data from both tables you must go for subqueries, 'cause that way the engine will only use the keys from the subqueries for the join, instead of the complete table. Never use your (PHP)-code to cache database results for the next query. If things are getting to complex to get done in one go use temporary table.

Upvotes: 1

Jon Gauthier
Jon Gauthier

Reputation: 25582

Use a relational model between libraries and books and query it using joins. This is fairly fast, as long as you have the primary keys and indexes for library_id set up.

table: libraries
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
+------+------+
| id   | name |
+------+------+
|    1 | Foo  |
|    2 | Bar  |
+------+------+

table: books
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI | 0       |       |
| library_id | int(11)      | NO   | MUL | NULL    |       |
| author     | varchar(100) | NO   |     | NULL    |       |
| name       | varchar(100) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
+----+------------+--------+------+
| id | library_id | author | name |
+----+------------+--------+------+
|  1 |          1 | Jon    | Baz  |
|  2 |          1 | Bill   | Baz  |
|  3 |          2 | Mary   | Abc  |
+----+------------+--------+------+

This allows for very easy querying. To find all books in the library with the name of 'Foo', you'd use a query like this:

mysql> SELECT books.* FROM books
    -> JOIN libraries ON libraries.id = books.library_id
    ->  AND libraries.name = 'Foo';
+----+------------+--------+------+
| id | library_id | author | name |
+----+------------+--------+------+
|  1 |          1 | Jon    | Baz  |
|  2 |          1 | Bill   | Baz  |
+----+------------+--------+------+

If you already know the library ID, you don't even need a join:

mysql> SELECT * FROM books
    -> WHERE library_id = 2;
+----+------------+--------+------+
| id | library_id | author | name |
+----+------------+--------+------+
|  3 |          2 | Mary   | Abc  |
+----+------------+--------+------+

Upvotes: 2

Pascal MARTIN
Pascal MARTIN

Reputation: 401032

I would go for :

  • A single SQL query
  • With an inner join between the books and library

After all, that's the most logical thing to do, in this kind of situation, considering :

  • You are using a relational database system
  • And your data seems properly structured.


If you want to optimize things a little, yes, you could use your last point ; but not sure that would change much actually :

  • You would send two queries to the database
  • Using the correct indexes (you probably are, if using foreign keys and InnoDB), one single query should work quite fast.

If I were to add some cache, I would probably cache the result of the whole book-search.

Upvotes: 0

Related Questions