MYSQL Databases and scalability + More

Databases
1

If we host a script that is dependent mostly on DB query for MYSQL. If the # of queries per hour or seconds is/are huge does MYSQL have a capacity to handle huge scalability?

No. Then what are the possible alternatives to MYSQL that can be integrated with the PHP?

If yes, then is “Yes” a yes with some constraints or dependent on server capacities?

2

Completely depends on the data and the queries. In general, when used correctly, MYSQL scales very well, but only when used correctly.

When used incorrectly it can come a grinding halt quite quickly.

3

It it possible to guide me more on this? Or understanding is subjective?

4

It’s more an “it depends”

Starting with the data, database engine and the database design.

IMHO, the trick is knowing common gotcha’s and gaining earned experience.

eg. complex queries on large tables that do full table scans should be avoided.

I think if you get in the habit of running EXPLAIN on your queries while working them up it will help you.

As for “can MySQL handle enterprise” I’m totally guessing but I don’t think it would become a problem until the load got a lot more than even a moderately large and popular site. I’m also guessing that by the time most sites get to that point they’ve had time to adjust where needed.

5

It’s hard to get specific, but these are a few things that are likely to become problematic given enough data and enough time:

  • queries with nested subqueries
  • queries with self joins
  • queries with too many joins
  • not enough indices (makes SELECT really slow)
  • too many indices (makes mutations like INSERT and UPDATE really slow)
  • using functions in WHERE and/or HAVING clauses (making the query not sargable1)
  • not tuning MySQL at all in terms of memory etc
  • SELECT ALL THE THINGS LIMIT 3

There’s probably more, and not all of these will always be problematic. YMMV.

1 if you don’t know what it means, google it :slight_smile:

6

Another that comes to mind is understanding pattern searching. eg.

“ILIKE '*sumtn'” is more expensive than “LIKE 'sumtn*'”

Depending, the inefficiency might be acceptable I guess. If a table is almost assuredly going to always be relatively small / infrequently queried and there are more urgent needs maybe a comment like

# Hey, sorry for the burn bro. bottom line and all that

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.