CODEWITHSUNDEEP
mysqlsqloptimization
iordanis
iordanis

Reputation: 1284

MySQL search Optimization for Search Fields

I have an MySQL database with 500,000 rows.

I have a list of 500,000 combination Strings such as First_Name and Last_Name.

I am trying to search the 500,000 rows with a similar query

select count(*) FROM data WHERE first='wadaw' AND last='wdvv';

The problem is that it takes too much time, I am using multiple threads and it doesn't seem to be very efficient considering the communication overhead between MySQL and the running time of the queries. I thought to start improve by changing the settings of my database to better fit my data and optimize MySQL database for it.

From my experience with search algorithms, an unsorted list would take n*log(n) with the most widely used methods and N with radix sort etc. which makes it n^2 or n^2log(n) for my case, which is not that good if you have 1,000,000 fields.

But with Binary search it would take Log(n) and thus n*log(n) for my case.

I am looking for a way to make the best out of my database.

Any suggestions?

Upvotes: 0

Views: 79

Answers (1)

lalborno
lalborno

Reputation: 424

Try using an index for both fields you are using. In your example:

create index idx_data_name_last on data (first, last);

That will use just one index and so the time will be log(n) and not n*log(n).

Upvotes: 1

Related Questions