CODEWITHSUNDEEP
mysql
terjanq
terjanq

Reputation: 311

Optimization for search

I'm working on a project and I have some problem with optimization in MySQL. My main table looks like and have around 1M rows:

+----+------+---------+ 
| id | Name | city_id |  City_id is between (0, 2000).
+----+------+---------+

I'll make many queries like:

  1. SELECT * FROM table WHERE city_id=x
  2. SELECT * FROM table WHERE city_id=x AND id=rand()

It is only to show you main operations on this database

If i'll make 2k small tables will it be good solution?

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think the solution you are looking for is an index. Try this:

create index idx_table_city_id on table(city_id, id);

SQL is designed to handle large tables. There are very few reasons why you would want to split up data from one table to multiple tables. The only good reason I can think of are when doing so is needed to meet security requirements.

Upvotes: 3

Related Questions