Geek asks and answers

Extremely Slow MySQL Query

I have two tables in MySQL, table1 has 1 013 347 objects and 38 attributes, and table2 has 7343,905 entities and 10 attributes. In the next query (which should get the number of rows for pagination) table1.ID is PK, table2.ID is its FK (both are indexed), and the HAVING clause gets a value if it exceeds a certain percentage, in this case 50%

SELECT SQL_CALC_FOUND_ROWS * 
FROM table1 INNER JOIN table2 ON table1.ID = table2.ID 
WHERE table1.attribute1 LIKE 'D%' 
GROUP BY table2.ID 
HAVING (COUNT(table2.ID) * (100/18)) >= '50'

Even in the simplified state that I posted here, this request takes at least 5 minutes to run through the command line. I know that I have to make changes to the request, the PHP code (the values ​​"50" and "D" are assigned via PHP variables) and / or to my MySQL configuration in order to speed things up (I use the latest XAMPP with default settings). Any help would be greatly appreciated.

EDIT1: All TINYTEXT attributes, except for the ID attributes, which are VARCHAR (9).

EDIT2: EXPLAIN SELECT ... returns:

+----+-------------+--------+------+---------------+-------------+---------+------+---------+---------------------------------+
| id | select_type | table  | type | possible_keys | key         | key_len | ref  | rows    | Extra                           |
+----+-------------+--------+------+---------------+-------------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | table2 | ALL  | NULL          | NULL        | NULL    | NULL | 7343905 | Using temporary; Using filesort |
|  1 | SIMPLE      | table1 | ref  | ID            | ID          | 29      | func |       1 | Using where                     |
+----+-------------+--------+------+---------------+-------------+---------+------+---------+---------------------------------+
2 rows in set (0.00 sec)
+3
source share
2 answers

Here are some potential improvements:

  • VARCHAR (9), , . varchars, . . .
  • LIKE . ; , table1.attribute1.
  • , , LIKE : , 'D%' RIGHT(), , . , , table1.attribute1 pre-cut; , LIKE php script.
+3

  • table2.ID() table1.ID
  • , id bigint table1.attribute1 varchar. , varchar .

  • , SQL (100/18),

    HAVING (COUNT (table2.ID) * (5.5555)) >= 50

( table2.ID , )

, , , , , table1.attribute1.

,

+1

More articles:


All Articles