[Solved] Design a database for shop

Question

A good way with MySQL to speed up some selections is create something called Indexes.

If you have a large database with many rows and only need to query certain columns, creating an index of that column speeds things up drastically when it comes to complex queries.

An example is this: CREATE INDEX index_name ON table_name (column1, column2, ...);

Here’s an example from one of my projects where it comes in handy, I created an index for the email column

SELECT 
    emails.Table.email AS email,
    COALESCE(emails.Table.clicks, 0) AS clicks
    FROM emails.Table
    LEFT OUTER JOIN otherDB.OtherTable on emails.Table.email = otherDB.OtherTable.email
    GROUP BY email, clicks
    ORDER BY clicks

The COALESCE function replaces any nil values with 0 in my case.

Hope this helps!

Accepted Answer