How to optimize slow SELECT SELECT queries?
Here is my request:
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
WHERE col1= 'val' and col7='Y' and col16='203' OR col16='201' order by col4 desc
I don't know what makes this query slow, be it order by
or where clauses ...
Also added an index, but it's still slow.
I am using JSP + STRUTS + EJB2.0 + MYSQL. Table 1 contains over half a million records. How can I optimize the query or what other features can improve the execution speed?
Table structure
col1 varchar(20) NO PRI
col2 varchar(50) NO PRI
col3 varchar(50) YES [NULL]
col4 varchar(20) YES [NULL]
col5 varchar(6) YES [NULL]
col6 varchar(20) YES [NULL]
col7 varchar(1) YES [NULL]
col8 mediumtext YES [NULL]
col9 mediumtext YES [NULL]
col10 mediumtext YES [NULL]
col11 mediumtext YES [NULL]
col12 mediumtext YES [NULL]
col13 mediumtext YES [NULL]
col14 mediumtext YES [NULL]
col15 mediumtext YES [NULL]
col16 varchar(20) YES [NULL]
col17 varchar(50) YES [NULL]
col18 varchar(5) YES [NULL]
col19 varchar(5) YES [NULL]
col20 varchar(5) YES [NULL]
col21 text YES [NULL]
col 22 text YES [NULL]
col23 text YES [NULL]
col24 varchar(5) YES [NULL]
col25 int(11) YES [NULL]
source to share
I don't know what makes this query slow, be it the order or the conditions.
Half a million entries should fit into memory if they are a common name, phone number, email (not documents). Therefore, if he is very slow, something is very wrong.
correctly added index as well, still slow.
What columns are indexed? You need to index the column that filters the most efficiently. For example, if col2 is the answer to a yes or no question, it will not help index it.
source to share
Your request -
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
where
col1= 'val'
and col2='Y'
and (col3='203' OR col3='201')
order by col4 desc
First you need a coverage index
alter table table1 add index search_idx(col1,col2,col3) ;
Now, to allow the order by clause, you will also need to specify it
alter table table1 add index col4_idx(col4) ;
Now note that the condition or
is killer and performace wise it is better to convert it tounion all
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
where
col1= 'val'
and col2='Y'
and col3='203'
union all
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
where
col1= 'val'
and col2='Y'
and col3='201'
order by col4 desc
You can use explain select
for the above queries to analyze the status of the request.
Be sure to back up the table before applying the index.
https://dev.mysql.com/doc/refman/5.0/en/select-optimization.html
https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
source to share
Are you sure your WHERE clause is correct?
And takes precedence over OR, so
where col1= 'val' and col2='Y' and col3='203' OR col3='201'
coincides with
where (col1= 'val' and col2='Y' and col3='203') OR (col3='201')
But you probably want
where col1= 'val' and col2='Y' and (col3='203' OR col3='201')
Edit:
Based on your comment, my assumption was wrong and you really want the original output (then I would suggest adding parentheses to make it clear). The only possible index in this case would be on col3
(if it is selective enough).
I don't know if the mysql optimizer is sufficient to access the table using the same index twice, unless you need to UNION ALL two queries:
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
WHERE col1= 'val' AND col2='Y' AND col3='203'
UNION ALL
SELECT col1, col2, col3, col4, col5, col6,col7, col8,..,col18
FROM table1
WHERE col3='201'
ORDER BY col4 DESC
Edit2: After the OP edited the question, the column names are wrong ( col2
will col7
and col3
will col16
)
source to share