Mysql table with performance problem (Too slow)

Asked

Viewed 72 times

0

I have a MYSQL table with only 31,000 records and she’s very slow when I’m going to consult, but I have no idea how to fix it. This is her creation script:

CREATE TABLE `PHOTOS_Data_Base` (
    `PHOTOS_Data_Base_ID` int(11) NOT NULL AUTO_INCREMENT,
    `PHOTOS_Data_Base_keywords` text NOT NULL,
    `PHOTOS_Data_Base_description` text NOT NULL,
    `FK_CONTRIBUTORS_User` int(11) NOT NULL,
    `PHOTOS_Data_Base_current_file_name` tinytext NOT NULL,
    `PHOTOS_Data_Base_original_file_upload_date` datetime NOT NULL,
    `PHOTOS_Data_Base_process_date` datetime NOT NULL,
    `PHOTOS_Data_Base_click_date` datetime NOT NULL,
    `PHOTOS_Data_Base_digitalization_date` datetime NOT NULL,
    `PHOTOS_Data_Base_resolution` smallint(6) NOT NULL,
    `PHOTOS_Data_Base_width` smallint(6) NOT NULL,
    `PHOTOS_Data_Base_height` smallint(6) NOT NULL,
    `PHOTOS_Data_Base_extension` tinytext NOT NULL,
    `PHOTOS_Data_Base_make` tinytext NOT NULL,
    `PHOTOS_Data_Base_model` tinytext NOT NULL,
    `PHOTOS_Data_Base_lens_model` tinytext NOT NULL,
    `PHOTOS_Data_Base_orientation` tinytext NOT NULL,
    `PHOTOS_Data_Base_components_number` tinyint(4) NOT NULL,
    `PHOTOS_Data_Base_metadata_JSON` text NOT NULL,
    `PHOTOS_Data_Base_title` tinytext,
    `PHOTOS_Data_Base_file_size` float(5,2) DEFAULT NULL,
    `PHOTOS_Data_Base_original_file_name` varchar(30) DEFAULT NULL,
    `FK_PHOTOS_Data_Base_Origin` int(11) NOT NULL,
    `FK_PHOTOS_Data_Base_Status` int(3) NOT NULL,
    `FK_PHOTOS_Type` int(11) NOT NULL,
    PRIMARY KEY (`PHOTOS_Data_Base_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2107242773 DEFAULT CHARSET=utf8;

A simple SELECT takes more than 1 minute to bring the information:

SELECT * FROM Photos_data_base WHERE Fk_photos_data_base_status = 2 15 Row(s) returned 0.188 sec / 88.609 sec

Is there any MYSQL feature to speed up this table? Because in the future this table will receive millions of information and even with 30,000 records is already too slow...

mysql
  • 3

    you created the FK FK_PHOTOS_Data_Base_Status ? tries to create an index for that column

  • 1

    @Rovannlinhalis I’ll do the alter table and see the result

  • 2

    I don’t know about the problem yet, but I couldn’t use simpler names? smallint(6)? I’m afraid of that AUTO_INCREMENT. There’s a resource, it’s called index, you know?

  • @Maniero I created this table 1 year and 3 months ago, I was 100% raw in development so I tried to create a pattern for the names, but today I end up getting confused... About the smallint(6) it was phpMyAdmin that created automatically. AUTO_INCREMENT It’s about that size because my boss asked me to start a specific number. I know a bit of Indian, but I never applied, I believe that this is the moment.

  • what is PHOTOS_Data_Base_metadata_JSON ?? is the binary of the file ??

  • 1

    @Rovannlinhalis is a JSON with image file metadata. information like IPTC header, JFIF, EXIF, File System and etc.

  • @Maniero take a look at this question https://answall.com/q/310488/77723

  • @Leonardobonetti index is all in DB, without it I don’t even think in DB.

  • 1

    You do not need to change the table with alter table to check performance, create an index with the create index command Data_base_status on Photos_data_base(Fk_photos_data_base_status); then run select

Show 4 more comments

1 answer

3


Create indexes pointing to the most commonly used fields as the search parameter and if so, make sure that foreign keys are properly referenced. The importance of the existence of indices and foreign key is that these serve as a "beacon" of the table, which helps the database service to find the records with more accuracy and speed when the Where parameters are with these indexes referenced.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.