0

My team handles a tool that automatically detects and categorizes images.We have a Mysql DB (InnoDB engine) used by our tool in production where we store information about each image processed.

The table is poorly designed by someone long before I joined the team. It was all working well as there was very less data in Db till now. Recently we launched the tool to a wider audience and the Db is now having huge data. The 'select' query speed is very slow and takes days to get result.

I am not an expert in Databases. Please help on suggestions to improve the performance. I am thinking of options like creating indexes. We cannot have downtime for the system.Th Db is write heavy (around 200 insertion per second). I am afraid of creating table locks if i try out index creation.

Table schema: no primary Key, no indexes

CREATE TABLE `ImageDetails` (
  `BOOKNAME` varchar(11) DEFAULT NULL,
  `PREV_ID` varchar(11) DEFAULT NULL,
  `TIMESTAMP` varchar(100) DEFAULT NULL,
  `IMAGE_FILENAME` varchar(1000) DEFAULT NULL,
  `POSITION_ID` varchar(1000) DEFAULT NULL,
  `PRECEDING_TEXT` varchar(3000) DEFAULT NULL,
  `WIDTH` float DEFAULT NULL,
  `HEIGHT` float DEFAULT NULL,
  `IMAGE_ORIENTATION` varchar(100) DEFAULT NULL,
  `COMPUTED_WIDTH` float DEFAULT NULL,
  `COMPUTED_HEIGHT` float DEFAULT NULL,
  `SRC_STYLE_WIDTH` varchar(100) DEFAULT NULL,
  `SRC_STYLE_HEIGHT` varchar(100) DEFAULT NULL,
  `LAYOUT_TYPE` varchar(30) DEFAULT NULL,
  `COVER` varchar(10) DEFAULT NULL,
  `TYPE` varchar(250) DEFAULT NULL,
  `TYPE_SCORE` float DEFAULT NULL,
  `CATEGORY` varchar(500) DEFAULT NULL,
  `CATEGORY_SCORE` float DEFAULT NULL,
  `HISTORIC_SCORE` float DEFAULT NULL,
  `QUALITY_SCORE` float DEFAULT NULL,
  `NEW_WIDTH_IN_PERCENT` float DEFAULT NULL,
  `NEW_MAX_WIDTH_IN_POINTS` float DEFAULT NULL,
  `NEW_HEIGHT_IN_PERCENT` float DEFAULT NULL,
  `NEW_MAX_HEIGHT_IN_POINTS` float DEFAULT NULL,
  `ORIGINAL_WIDTH` float DEFAULT NULL,
  `ORIGINAL_WIDTH_UNIT` varchar(100) DEFAULT NULL,
  `ORIGINAL_HEIGHT` float DEFAULT NULL,
  `ORIGINAL_HEIGHT_UNIT` varchar(100) DEFAULT NULL,
  `CONTAINER_HIERARCHY_TYPE` varchar(1000) DEFAULT NULL,
  `ERROR_CODE1` varchar(1000) DEFAULT NULL,
  `ERROR_CODE2` varchar(1000) DEFAULT NULL,
  `STATUS` varchar(1000) DEFAULT NULL,
  `CLIENT` varchar(100) DEFAULT NULL,
  `SAMPLE` tinyint(1) DEFAULT NULL,
  `T_ID` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

EDIT:

Mysql Engine version- 8.0.17

200 insert statements per second

The select query are of the form:

-- 1.
select count(BOOKNAME) 
from table 
where timestamp between time1 and time2.

-- 2.
select count(BOOKNAME) 
from table 
where (timestamp between time1 and time2) 
    and status="success";

-- 3.
select count(BOOKNAME) 
from table 
where (timestamp between time1 and time2) 
    and (type like "%landscape%" ) 
    and status="success";
6
  • 1
    Could you please update your post with the type of SELECT queries (among other common queries) that are normally ran against the Table? When you say "200 insertion per second" do you mean 200 rows inserted per second or 200 different INSERT statements happen at the same time per second? How many rows total is the Table? Do any combination of the 6 columns guarantee uniqueness? How big is each VARCHAR column defined as? What version of MySQL are you using? Please answer all of these in an update to your post, as they are important for us to provide an accurate answer. – J.D. Mar 9 at 3:48
  • 1
    Add complete SHOW CREATE TABLE tablename; for all tables. Add all queries which really need in performance improve. – Akina Mar 9 at 5:06
  • Certain index changes do not block access. How big is the table? – Rick James Mar 9 at 7:32
  • nearly 200 million rows are present now. – lonewolf Mar 9 at 7:41
  • The indexing you can get away with will depend on how large your timestamp ranges are. If they are small then you can just index the timestamp column and all those queries should speed up massively. Why would bookname be null - should you be able to do a count(*) instead and not check that the rows have a not null book name? I assume there are only a few distinct values that the status column could take? – Andrew Sayer Mar 9 at 8:25
0

You can try creating one single fully covering index, based on the queries you provided, on the fields timestamp, type, status, bookname. This should be applicable to each of the three queries above, but knowing if it's ideal or the best index would be dependent on your table's data and testing it. As Andrew points out, depending on how large the timestamp ranges are, and more so meaning depending on how many rows the typical range returns, will affect the selectivity of indexing on that column. But after you create the index, you should be able to test and run an EXPLAIN and ANALYZE and see if it's using the index appropriately.

Note for a 200 million row table, it might take a little bit to create the index, and you might want to do it during a maintenance window, off-hours. Also you should look to use proper data types and clean up your table when possible as well, for example timestamp should be stored in a timestamp type column, and does the status column really need up to 1,000 characters when there's only 6 distinct values for status?...these minor things could also affect performance in minor ways, and definitely affect maintainability, but this is more of a sidenote.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.