SEO

I have something I am trying to resolve with an over abundant number of
slow queries. Perhaps it is because of some additional indexes needed. As
soon as I enabled the option 'log_queries_not_using_indexes = 1' in the
configuration file, I started getting messages relating to the select
query:

SELECT wite_what, wite_desc FROM witelist
WHERE
wite_what = '$oct1' OR
wite_what = '$oct1.$oct2' OR
wite_what = '$oct1.$oct2.$oct3' OR
wite_what = '$oct1.$oct2.$oct3.$oct4' OR
wite_what = '$from_name\@$from_dom' OR
wite_what = '$rcpt_name\@$rcpt_dom' OR
wite_what = '$from_dom' OR
wite_what = '$rcpt_dom';

The table used is defined as such:

mysql> desc witelist;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| wite_what | varchar(128) | NO | UNI | NULL | |
| wite_desc | varchar(128) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+

Can anyone shed some light if I should index wite_desc to speed things up?

-d

On Wed, Apr 23, 2008 at 9:22 PM, D Hill <d.hill@yournetplus.com> wrote:
> Can anyone shed some light if I should index wite_desc to speed things up?

No, since you don't use that column at all. If you're not on MySQL 5,
upgrading to MySQL 5 will help. Otherwise, you're best bet is to
rewrite the query as UNION clauses with one of your WHERE conditions
in each. I know it sounds crazy, but before MySQL 5 the use of
indexes with OR queries was not very good.

- Perrin

Perrin Harkins schrieb:
> On Wed, Apr 23, 2008 at 9:22 PM, D Hill <d.hill@yournetplus.com> wrote:
>> Can anyone shed some light if I should index wite_desc to speed things up?
>
> No, since you don't use that column at all. If you're not on MySQL 5,
> upgrading to MySQL 5 will help. Otherwise, you're best bet is to
> rewrite the query as UNION clauses with one of your WHERE conditions
> in each. I know it sounds crazy, but before MySQL 5 the use of
> indexes with OR queries was not very good.

IMHO not in this case, cause it is just a simple "WHERE field IN ()"

--
Sebastian Mendel

D Hill schrieb:
>
> I have something I am trying to resolve with an over abundant number of
> slow queries. Perhaps it is because of some additional indexes needed.
> As soon as I enabled the option 'log_queries_not_using_indexes = 1' in
> the configuration file, I started getting messages relating to the
> select query:
>
> SELECT wite_what, wite_desc FROM witelist
> WHERE
> wite_what = '$oct1' OR
> wite_what = '$oct1.$oct2' OR
> wite_what = '$oct1.$oct2.$oct3' OR
> wite_what = '$oct1.$oct2.$oct3.$oct4' OR
> wite_what = '$from_name\@$from_dom' OR
> wite_what = '$rcpt_name\@$rcpt_dom' OR
> wite_what = '$from_dom' OR
> wite_what = '$rcpt_dom';

did you tried (result depending on your MySQL version):

WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
'$oct1.$oct2.$oct3.$oct4', '$from_name\@$from_dom',
'$rcpt_name\@$rcpt_dom', '$from_dom', '$rcpt_dom');

you could also vary with thee index length if wite_what.

and what indexes do you have currently exactly?

--
Sebastian Mendel

On Thu, 24 Apr 2008 at 08:58 +0200, lists@sebastianmendel.de confabulated:

> D Hill schrieb:
>>
>> I have something I am trying to resolve with an over abundant number of
>> slow queries. Perhaps it is because of some additional indexes needed. As
>> soon as I enabled the option 'log_queries_not_using_indexes = 1' in the
>> configuration file, I started getting messages relating to the select
>> query:
>>
>> SELECT wite_what, wite_desc FROM witelist
>> WHERE
>> wite_what = '$oct1' OR
>> wite_what = '$oct1.$oct2' OR
>> wite_what = '$oct1.$oct2.$oct3' OR
>> wite_what = '$oct1.$oct2.$oct3.$oct4' OR
>> wite_what = '$from_name\@$from_dom' OR
>> wite_what = '$rcpt_name\@$rcpt_dom' OR
>> wite_what = '$from_dom' OR
>> wite_what = '$rcpt_dom';
>
> did you tried (result depending on your MySQL version):
>
> WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
> '$oct1.$oct2.$oct3.$oct4', '$from_name\@$from_dom',
> '$rcpt_name\@$rcpt_dom', '$from_dom', '$rcpt_dom');

MySQL version is 5.0.51. Sorry I forgot to mention that. I did change the
query to what you have shown. I'll have to wait till the server comes
under a load to tell. I have noted when the last slow query was logged for
this and will see.

> you could also vary with thee index length if wite_what.

Right now the index is for the full length of the field (128). I just ran
a query for the length of wite_what and the maximum length so far is 34.
So, I will cut the index length down to 64.

> and what indexes do you have currently exactly?

id - is the primary and has an index type btree
wite_what - is a unique and has an index type of btree

On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel
<lists@sebastianmendel.de> wrote:
> IMHO not in this case, cause it is just a simple "WHERE field IN ()"

I'm pretty sure that just looks like a bunch of ORs to MySQL. If it
didn't use the index with OR, it won't use it with IN.

What usually works is to change it to UNION:

SELECT wite_what, wite_desc FROM witelist
WHERE wite_what = '$oct1'
UNION
SELECT wite_what, wite_desc FROM witelist
WHERE wite_what = '$oct1.$oct2'
UNION
....etc.

- Perrin

On Thu, 24 Apr 2008 at 10:16 -0400, perrin@elem.com confabulated:

> On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel
> <lists@sebastianmendel.de> wrote:
>> IMHO not in this case, cause it is just a simple "WHERE field IN ()"
>
> I'm pretty sure that just looks like a bunch of ORs to MySQL. If it
> didn't use the index with OR, it won't use it with IN.
>
> What usually works is to change it to UNION:
>
> SELECT wite_what, wite_desc FROM witelist
> WHERE wite_what = '$oct1'
> UNION
> SELECT wite_what, wite_desc FROM witelist
> WHERE wite_what = '$oct1.$oct2'
> UNION
> ...etc.

I'm still new to MySQL. The input is greatly appreciated.

It took some minor thought and the documentation, but I understand what is
going on with the UNION.

D Hill schrieb:
> On Thu, 24 Apr 2008 at 08:58 +0200, lists@sebastianmendel.de confabulated:
>
>> D Hill schrieb:
>>>
>>> I have something I am trying to resolve with an over abundant number
>>> of slow queries. Perhaps it is because of some additional indexes
>>> needed. As soon as I enabled the option
>>> 'log_queries_not_using_indexes = 1' in the configuration file, I
>>> started getting messages relating to the select query:
>>>
>>> SELECT wite_what, wite_desc FROM witelist
>>> WHERE
>>> wite_what = '$oct1' OR
>>> wite_what = '$oct1.$oct2' OR
>>> wite_what = '$oct1.$oct2.$oct3' OR
>>> wite_what = '$oct1.$oct2.$oct3.$oct4' OR
>>> wite_what = '$from_name\@$from_dom' OR
>>> wite_what = '$rcpt_name\@$rcpt_dom' OR
>>> wite_what = '$from_dom' OR
>>> wite_what = '$rcpt_dom';
>>
>> did you tried (result depending on your MySQL version):
>>
>> WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
>> '$oct1.$oct2.$oct3.$oct4', '$from_name\@$from_dom',
>> '$rcpt_name\@$rcpt_dom', '$from_dom', '$rcpt_dom');
>
> MySQL version is 5.0.51. Sorry I forgot to mention that. I did change
> the query to what you have shown. I'll have to wait till the server
> comes under a load to tell. I have noted when the last slow query was
> logged for this and will see.
>
>> you could also vary with thee index length if wite_what.
>
> Right now the index is for the full length of the field (128). I just
> ran a query for the length of wite_what and the maximum length so far is
> 34. So, I will cut the index length down to 64.
>
>> and what indexes do you have currently exactly?
>
> id - is the primary and has an index type btree
> wite_what - is a unique and has an index type of btree

so this looks all ok, i am not sure if the query time includes the time if
the query needs to wait for locked tables ...

--
Sebastian