MySQL table with performance problem (Very slow)

Question

MySQL table with performance problem (Very slow)

Navigation

#1 by (3 votes)

0

I have a MYSQL table with only 31,000 records and it is very slow when I am going to query, but I have no idea how to resolve this. Your creation script is this:

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 increase the speed of this table? For in the future this table will receive millions of information and even with 30,000 records it is already too slow ...

mysql

asked by anonymous 28.06.2018 / 16:28

1 answer

How to do if in sql - Postgres How do I assign the last value of a vector to a variable?

score 3 · Accepted Answer

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