Re: Index help
On Nov 12, 2007 9:22 AM, Afan Pasalic <[EMAIL PROTECTED]> wrote: > If you have to deal with it again consider using a bunch of unions > instead of the 'IN'. Not prettiest thing, but it should fix your > performance issue. > Could you please give me more details about your statement that mysql deals > not so well with IN, and it's better to use UNIONS? > I just tested two queries, using IN and UNIONS (using MySQL Browser) and > I'm getting almost identical download/fetched time? Someone call me on it if any of the following is incorrect: Prior to Mysql 5.0, queries using OR or IN would not use an index unless the left most portion of the index was the portion using the OR/IN. Mysql 5.0 and later can use a merge index to replicate a UNION (among other) optimization. Mysql treats each query in a union as a completely separate query. If the reason the query was not using an index was because of the IN or OR a unions might allow mysql to use the index. Look at the explain of the union query. Is it using an index on whichever column you are trying to optimize? I am going to guess it is not, or even if it is the query is not very efficient otherwise. What version of mysql are you on? What % of the entries would fulfill by the IN? Mysql used to choose to do a table scan if an index would not eliminate a significant (like 70%+) of the rows. The algorithm has since changed, but that would be a good rule of thumb. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index help
On Nov 12, 2007 7:57 AM, Stut <[EMAIL PROTECTED]> wrote:
> Hi Rob,
>
> Thanks for your reply.
>
>
> Rob Wultsch wrote:
> > On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote:
> >> Hi all,
> >>
> >> I've inherited a PHP app that uses a MySQL database. The following query
> >> is extremely slow and I've been battling for a couple of days on an off
> >> to try and get a combination of indexes to optimise it. Any help would
> >> be greatly appreciated.
> >>
> >>
> >> select household_d.id, household_d.ad_type, household_d.ad_catid,
> >> household_d.ad_renewed, household_d.ad_userid,
> >> household_d.ad_trade, household_d.price,
> >> SUBSTRING(household_d.description, 1, 301) as description,
> >> users.issuperseller, users.phone, users.town
> >> from household_d
> >> left join users on household_d.ad_userid = users.id
> >> where household_d.ad_catid in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
> >>and household_d.ad_status = "live"
> >>and household_d.id not in (1, 2)
> >> order by ad_renewed desc
> >> limit 0,14
> >>
> >>
> >> Explain currently states that it's using the primary key for the users
> >> table, and the following for the household_d table...
> >>
> >> select_type = SIMPLE
> >> type = ref
> >> key = ad_status
> >> ref = const
> >> key_len = 1
> >> key_len
> >> rows = 22137
> >> extra = Using where; Using filesort
> >>
> >> Running locally this query is pretty fast, but on the live site it is
> >> currently taking anything up to a minute. My limited knowledge of MySQL
> >> indexes led me to add an index with ad_catid, ad_status, ad_renewed and
> >> id, but explain only says it's a possible key, it doesn't actually use it.
> >>
> >> Any tips appreciated.
> >
> > A few things pop out at me:
> > 1. Mysql is known for not dealing with 'IN(' or 'OR' queries particulary
> > well.
> > Could you instead do something like:
> > where household_d.ad_catid BETWEEN 1 AND 10
> > ?
> > 1.1 Same deal with household_d.id
>
> Unfortunately not. Neither ad_catid nor id are sequential.
>
> > 2. I am going to guess that ad_status has very low cardinality. I
> > generally build up composite index's by creating an index on whatever
> > column I think would have the highest useful cardinality. I then test
> > it to make sure mysql actually uses the new index. If it does not I
> > figure out why. I then add another column to the index and test the
> > query to make sure extra length gets used and the query did not get
> > slower. Rinse, wipe, repeat.
>
> That's basically what I was doing, but I wasn't getting anywhere. Since
> I posted the question I've thrown more RAM at the server and it's a lot
> better now but I still worry that it's using where and filesort but it's
> possible there's no way to make it any quicker. I've not had any
> complaints about the speed since I put more RAM in.
>
> Thanks again.
>
> -Stut
>
If you have to deal with it again consider using a bunch of unions
instead of the 'IN'. Not prettiest thing, but it should fix your
performance issue.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index help
Hi Rob,
Thanks for your reply.
Rob Wultsch wrote:
On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote:
Hi all,
I've inherited a PHP app that uses a MySQL database. The following query
is extremely slow and I've been battling for a couple of days on an off
to try and get a combination of indexes to optimise it. Any help would
be greatly appreciated.
select household_d.id, household_d.ad_type, household_d.ad_catid,
household_d.ad_renewed, household_d.ad_userid,
household_d.ad_trade, household_d.price,
SUBSTRING(household_d.description, 1, 301) as description,
users.issuperseller, users.phone, users.town
from household_d
left join users on household_d.ad_userid = users.id
where household_d.ad_catid in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
and household_d.ad_status = "live"
and household_d.id not in (1, 2)
order by ad_renewed desc
limit 0,14
Explain currently states that it's using the primary key for the users
table, and the following for the household_d table...
select_type = SIMPLE
type = ref
key = ad_status
ref = const
key_len = 1
key_len
rows = 22137
extra = Using where; Using filesort
Running locally this query is pretty fast, but on the live site it is
currently taking anything up to a minute. My limited knowledge of MySQL
indexes led me to add an index with ad_catid, ad_status, ad_renewed and
id, but explain only says it's a possible key, it doesn't actually use it.
Any tips appreciated.
A few things pop out at me:
1. Mysql is known for not dealing with 'IN(' or 'OR' queries particulary well.
Could you instead do something like:
where household_d.ad_catid BETWEEN 1 AND 10
?
1.1 Same deal with household_d.id
Unfortunately not. Neither ad_catid nor id are sequential.
2. I am going to guess that ad_status has very low cardinality. I
generally build up composite index's by creating an index on whatever
column I think would have the highest useful cardinality. I then test
it to make sure mysql actually uses the new index. If it does not I
figure out why. I then add another column to the index and test the
query to make sure extra length gets used and the query did not get
slower. Rinse, wipe, repeat.
That's basically what I was doing, but I wasn't getting anywhere. Since
I posted the question I've thrown more RAM at the server and it's a lot
better now but I still worry that it's using where and filesort but it's
possible there's no way to make it any quicker. I've not had any
complaints about the speed since I put more RAM in.
Thanks again.
-Stut
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index help
On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I've inherited a PHP app that uses a MySQL database. The following query
> is extremely slow and I've been battling for a couple of days on an off
> to try and get a combination of indexes to optimise it. Any help would
> be greatly appreciated.
>
>
> select household_d.id, household_d.ad_type, household_d.ad_catid,
> household_d.ad_renewed, household_d.ad_userid,
> household_d.ad_trade, household_d.price,
> SUBSTRING(household_d.description, 1, 301) as description,
> users.issuperseller, users.phone, users.town
> from household_d
> left join users on household_d.ad_userid = users.id
> where household_d.ad_catid in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
>and household_d.ad_status = "live"
>and household_d.id not in (1, 2)
> order by ad_renewed desc
> limit 0,14
>
>
> Explain currently states that it's using the primary key for the users
> table, and the following for the household_d table...
>
> select_type = SIMPLE
> type = ref
> key = ad_status
> ref = const
> key_len = 1
> key_len
> rows = 22137
> extra = Using where; Using filesort
>
> Running locally this query is pretty fast, but on the live site it is
> currently taking anything up to a minute. My limited knowledge of MySQL
> indexes led me to add an index with ad_catid, ad_status, ad_renewed and
> id, but explain only says it's a possible key, it doesn't actually use it.
>
> Any tips appreciated.
A few things pop out at me:
1. Mysql is known for not dealing with 'IN(' or 'OR' queries particulary well.
Could you instead do something like:
where household_d.ad_catid BETWEEN 1 AND 10
?
1.1 Same deal with household_d.id
2. I am going to guess that ad_status has very low cardinality. I
generally build up composite index's by creating an index on whatever
column I think would have the highest useful cardinality. I then test
it to make sure mysql actually uses the new index. If it does not I
figure out why. I then add another column to the index and test the
query to make sure extra length gets used and the query did not get
slower. Rinse, wipe, repeat.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index help
Hi all, I've inherited a PHP app that uses a MySQL database. The following query is extremely slow and I've been battling for a couple of days on an off to try and get a combination of indexes to optimise it. Any help would be greatly appreciated. select household_d.id, household_d.ad_type, household_d.ad_catid, household_d.ad_renewed, household_d.ad_userid, household_d.ad_trade, household_d.price, SUBSTRING(household_d.description, 1, 301) as description, users.issuperseller, users.phone, users.town from household_d left join users on household_d.ad_userid = users.id where household_d.ad_catid in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and household_d.ad_status = "live" and household_d.id not in (1, 2) order by ad_renewed desc limit 0,14 Explain currently states that it's using the primary key for the users table, and the following for the household_d table... select_type = SIMPLE type = ref key = ad_status ref = const key_len = 1 key_len rows = 22137 extra = Using where; Using filesort Running locally this query is pretty fast, but on the live site it is currently taking anything up to a minute. My limited knowledge of MySQL indexes led me to add an index with ad_catid, ad_status, ad_renewed and id, but explain only says it's a possible key, it doesn't actually use it. Any tips appreciated. -Stut -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index help
Horst Azeglio wrote:
I'm trying to do a MySQL Query but it doesn't work. MySQL version: 4.0.26
When I put only one argument in MATCH, it shows no error but doesn't return
anything
[quote]
SELECT * FROM item WHERE MATCH (nom) against ('Huile');[/quote]
or
[quote]SELECT * FROM item WHERE MATCH (nom_en) against ('Huile');[/quote]
When I put two arguments:
[quote]SELECT * FROM item WHERE MATCH (nom,nom_en) against ('Huile');
[/quote]
It says: "Can't find FULLTEXT index matching the column list", but both nom
and nom_en are FULLTEXT indexed and the table "item" is MyISAM.
You need another fulltext index that combines nom and nom_en.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT index help
I'm trying to do a MySQL Query but it doesn't work. MySQL version: 4.0.26
When I put only one argument in MATCH, it shows no error but doesn't return
anything
[quote]
SELECT * FROM item WHERE MATCH (nom) against ('Huile');[/quote]
or
[quote]SELECT * FROM item WHERE MATCH (nom_en) against ('Huile');[/quote]
When I put two arguments:
[quote]SELECT * FROM item WHERE MATCH (nom,nom_en) against ('Huile');
[/quote]
It says: "Can't find FULLTEXT index matching the column list", but both nom
and nom_en are FULLTEXT indexed and the table "item" is MyISAM.
Anyone can help? thank you
--
View this message in context:
http://www.nabble.com/FULLTEXT-index-help-t1771558.html#a4821912
Sent from the MySQL - General forum at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index help ?
I think the second can be better (more different values). But it contains almost the same data than the table. Try : explain Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 hour) AND message LIKE 'sshd%' GROUP BY machine; But an index with(date1, message, machine) sould be sufficient. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Michael Gale [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 05:49 To: mysql@lists.mysql.com Subject: Index help ? Hello, I have the following table setup: IDhostnamefacilityprioritydatemessage ID is auto incrementing. This is used to store all of the syslog messages, currently there are over 7 million: The following query takes forever: Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 hour) AND message LIKE 'sshd%' GROUP BY machine; I have created the following indexs but when I use Explain it says that the query has to search all the rows: datehostfacility 1 date1 A 352489 datehostfacility 2 machine A 1409956 datehostfacility 3 facility A 1409956 datemesghost 1 date1 A 640889 datemesghost 2 message(15) A 7049783 datemesghost 3 machine A 7049783 datemesghost 4 facility A 7049783 datemesghost 5 priority A 7049783 What would the proper index be ? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index help ?
Hello, I have the following table setup: IDhostnamefacilityprioritydatemessage ID is auto incrementing. This is used to store all of the syslog messages, currently there are over 7 million: The following query takes forever: Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 hour) AND message LIKE 'sshd%' GROUP BY machine; I have created the following indexs but when I use Explain it says that the query has to search all the rows: datehostfacility 1 date1 A352489 datehostfacility 2 machine A 1409956 datehostfacility 3 facility A 1409956 datemesghost 1 date1 A640889 datemesghost 2 message(15) A 7049783 datemesghost 3 machine A 7049783 datemesghost 4 facility A 7049783 datemesghost 5 priority A 7049783 What would the proper index be ? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
