Re: Partitioning throwing #1064 - Constant/Random expression in (sub)partitioning function is not allowed near ') ( PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2) PARTIT' at line 2 error

[Rick James]

Ouch, ouch and OUCH!

* Add an index!

* Maybe add another index.

* Avoid LIKE '%xxx%'; consider FULLTEXT index.

* CONCAT(UPPER(SUBSTRING(city, 1, 1)), LOWER(SUBSTRING(city FROM 2)))
will produce
Los angeles ?

* bigint -- only needed if you might get billions. Suggest INT, MEDIUMINT, etc.

Adding indexes will help more than partitioning!

Do this:

ALTER TABLE tbl_businesses
   ADD INDEX (city_id, state_code);

And see how that select performs.

Then show us some of your other queries.

Back to "Los angeles" -- Recommend you put the correct spelling into the table.