All geek questions in one place

PHP and MySQL: database optimization

I have a database with over 10,000,000 rows. A query right now may take a few seconds to find basic information. This is not preferable, I know that the best way to optimize is to minimize the number of lines that is possible, but now I do not have time for this.

What is the easiest way to optimize a MySQL database so that when it is requested, time is short?

I do not mind the size of the database, it does not really matter, so any optimizations that increase the size are good. I'm not very good at optimization, right now I have indexes configured, but I'm not sure how much better I can get there.

I will ultimately pick the right database, but is there a quick workaround?

+3
source share
6 answers

In addition to indexing, which has already been proposed, you can also look at the partition tables, if they are large.

Partitioning in MySQL

It is difficult to be specific here, because we have very limited information, but the correct indexing along with the separation can take a very long time. Correct indexing can be a long topic, but in a very general sense, you need to index the columns you are querying for.

For example, let's say you have a table of employees, and you have regular columns SSN, FNAME, LNAME. In addition to these columns, we will say that you have 10 more columns in the table.

:

SELECT FNAME, LNAME FROM EMPLOYEES WHERE SSN = 'blah';

, SSN, , , , , , , , (SSN, FNAME, LNAME). , , , , , , . (.. -). , SSN , (-), (-), , (: -), fname lname.

, , , - .

:

+2

, 40k , , .

, " ". "" . .

+2

, .

+1

"WHERE x = y", y - , y.

- № , 3% ~ 5%, , .

. , , DBA. DBA , .

+1

, . , , x.name = 'y' OR x.phone = 'z', , , . , , :)

+1

Are your queries using your indexes? What is it about using EXPLAIN in your favorite queries?

The first (and easiest) step will ensure that your queries are optimized.

0
source

Source: https://habr.com/ru/post/1735455/

More articles:


All Articles