Query Optimization
Hello,
I have a table (logs, a total of 10,688,627 entries) with the following columns:
id (bigint(20), UNSIGNED, auto_increment),
host int(11),
facility int(11),
priority int(11),
level int(11),
tag int(11),
timestamp int(10),
program varchar(50),
msg text
----------------------------------------------------------------------------------
The indices are:
PRIMARY (PRIMARY, 10677097, id),
host (INDEX, 15187, host),
priority (INDEX, 7, priority),
timestamp (INDEX, 1779516, timestamp)
----------------------------------------------------------------------------------
The query:
SELECT * FROM `logs` WHERE `msg` LIKE '%abrupt%'
returns 14 records in total and takes 33.4280 seconds.
----------------------------------------------------------------------------------
The parameters for the SQL Server (Linux, 2GB RAM):
key_buffer = 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 512M
optimizer_search_depth = 0
tmp_table_size = 64M
----------------------------------------------------------------------------------
What can I do to optimize my query?
Yours sincerely, and in the hope of help,
Nils
Edit:
In another forum, someone claimed my query may not use an index, I would have to delete the first placeholder. If this is true, that would be extremely disadvantageous, because it would affect the result.
I have now a full-text index on the relevant field (which incidentally lasted approximately 1.5 hours, however, the cardinality of this index is 0, which surprised me, because I assumed that the cardinality would correspond to the number of rows of the table. However, the result is: the query time is even longer! I'm trying it again now with a "normal" index ... let's see if it helps!
Edited 1 time(s). Last edit at 06/22/2009 07:08AM by Nils Heldt.