How to substantially decrease mySQL query processing time?

22
January 13, 2019, at 02:40 AM

I have a trouble that my query in mySQL is being processing about 8-10 minutes before report finally appears. I have created few indexes and time has fallen till 2-3 minutes. But that's seems too long either.

Is it some way to make some integration with ElasticSearch, Sphinx or something like this to support mySQL in making difficult requests.

  1. There are about 10 tables, each is 30-40k rows with 9-10 columns.
  2. Full DB size is less than 100M. Data mainly are INT, DOUBLE or VARCHAR.
  3. Request is something like this:

    SELECT SUM(`column1`) AS `sum` 
    FROM `table1` LEFT JOIN `table2` ON `table1`.`id` = `table2`.`id` 
    WHERE `column2` = ?s 
      AND `table2`.`datetime` BETWEEN STR_TO_DATE(?s, '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(?s, '%Y-%m-%d %H:%i:%s')```
    
READ ALSO
How to get multiple rows/columns matched from another table in mysql

How to get multiple rows/columns matched from another table in mysql

I have a records table and a history table that adds timestamps as records flow through our systemThey look something like this

19
Trouble with creating a table on jsp page having a column with blob images from MySQL

Trouble with creating a table on jsp page having a column with blob images from MySQL

I am trying to create a table with a column of text description of an image and a second column with blob images from MySQL

34
get hourly results for count in the last 12 hours - MySql

get hourly results for count in the last 12 hours - MySql

I have a big query that is supposed to return the count for rfid's for each product have an association with one order

24