I have a table:
mysql> describe table_1;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------------------+----------------+
| id | int(12) | NO | PRI | NULL | auto_increment |
| date | datetime | YES | | 0000-00-00 00:00:00 | |
| col1 | tinyint(1) | YES | | 1 | |
| col2 | longtext | YES | | NULL | |
| col3 | tinyint(1) | YES | | 1 | |
+---------------+------------------+------+-----+---------------------+----------------+
with the following characteristics:
mysql> select distinct(col1), count(col1) from table_1 group by col1;
+--------+---------------+
| col1 | count(col1) |
+--------+---------------+
| 0 | 200 |
| 1 | 689747 |
+--------+---------------+
How can I optimize queries like these, and with what kind of indexes?
1. SELECT * FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10;
2. SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND ( col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5;
Best Answer
At the risk of stating the obvious, you have a cardinality problem with
col1
.Looking at the two queries you posted in the question:
you need good indexes that will accommodate these two queries. In your particular case, you will need just one index.
If the table is MyISAM, here is that index
If the table is InnoDB, here is that index
How will this index help? Although you have to live with the lopsided cardinality of
col1
, you improve each query's chances of being effectively searched.You could also take a chance on refactoring the queries. How ???
Perhaps you could collect the ids only then join the ids back to the original table.
QUERY #1
Here is the first query
Collect the ids and join back to table_1
QUERY #2
Here is the second query
Collect the ids and join back to table_1
I cannot make promises on the queries, but, at the very least, the index suggestion should help.
Give it a Try !!!