-4

How should I design the database to reach the best speed.

  1. Should I use empty or NULL?
  2. I have 3 tables. What is the quickest way to select? Left join? "Category": id name

"Subcategory" id subname cat_id

"Subsubcategory" id subsubname subcat_id

UPD. Thanks a lot for your help.

P.S. Why did you dislike my question? Yes, I'm noob, but I want to know. Thanks a lot.

Jackson
  • 3
  • 2

1 Answers1

0

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!

Noy
  • 62
  • 6
  • Thanks. What about empty and NULL? What is the best practice for using? – Jackson Feb 21 '18 at 13:14
  • @Jackson if there's no value, store NULL. Don't store empty strings instead. For one thing, you can't do that on non-varchar fields anyway, so be consistent. – ADyson Feb 21 '18 at 13:18
  • As ADyson said, definitely best storing NULL. Check out this stackoverflow question for more info on why: https://stackoverflow.com/questions/1267999/mysql-better-to-insert-null-or-empty-string – Noy Feb 21 '18 at 13:25
  • Ok. All the facts are based on usability. But what is the fastest way? :) – Jackson Feb 21 '18 at 13:31
  • Between NULL or empty? Pretty much no difference – Noy Feb 21 '18 at 13:33
  • Agreed, it really won't make any noticeable difference to anything performance-wise. But it will make your code and data easier to comprehend and less ambiguous. – ADyson Feb 21 '18 at 14:11