Re: Index question
- Original Message - From: Tompkins Neil neil.tompk...@googlemail.com Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. Explain is your friend. You should listen to it :-) It gives a nice idea of how the database interprets your query, so you can see where the bottlenecks are, for example what bits don't use indices or cause file sorts. The exact interpretation of it is an art, though, and there are many subtleties you only get by experience and reading documentation. It is not something that is quickly explained; it requires a good knowledge of how a database works on the inside. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Index question
- Original Message - From: Alex Schaft al...@quicksoftware.co.za If you have a table with columns A B, and might do a where on A or B, or an order by A, B, would single column indexes on A and B suffice or would performance on the order by query be improved by an index on A,B? Depends on usage :-) key (a, b) is good for where a=.. or where a=.. and b=.. key (b, a) is good for where b=.. or where b=.. and a=.. (note that the sequence of a and b in the where clause is not important) key (a), key (b) is good for where a=.. or where b=.. but will only use one index for where a=.. and b=... I think work is ongoing on having the parser use multiple indices, but I'm not sure where that's at. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Just to clarify having key indexes of (a,b) or (b,a) have no difference ? On 11 Oct 2011, at 09:36, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Alex Schaft al...@quicksoftware.co.za If you have a table with columns A B, and might do a where on A or B, or an order by A, B, would single column indexes on A and B suffice or would performance on the order by query be improved by an index on A,B? Depends on usage :-) key (a, b) is good for where a=.. or where a=.. and b=.. key (b, a) is good for where b=.. or where b=.. and a=.. (note that the sequence of a and b in the where clause is not important) key (a), key (b) is good for where a=.. or where b=.. but will only use one index for where a=.. and b=... I think work is ongoing on having the parser use multiple indices, but I'm not sure where that's at. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
The difference is based on the relative frequency of queries where a is important vs. where b is important. Either way, what will happen is that the index scan will isolate the first item mentioned, then scan the result set to isolate the second term. e.g. SELECT * FROM someTable WERE a = someValue and b = someOtherValue Step one isolates the matching a values. Step two walks through that resultset and examines each value of b. Since the values of b are already in memory, all it has to do is examine the index keys to find the matches of b. No additional disk read is required. Arthur
Re: Index question
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searching for 'a' in an index
(b,a) requires a full scan. See it as looking through a phonebook trying to
locate someone by first- rather then lastname. It's in there, just not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad for ONLY
a
- index (a) index (b) is good for searches on ONLY b or ONLY a, and is
suboptimal for searching for BOTH a,b (although, faster then no index, but the
query optimizer has to choose which index to use, can't use both).
--
Rik Wasmus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searching for 'a' in an index
(b,a) requires a full scan. See it as looking through a phonebook trying to
locate someone by first- rather then lastname. It's in there, just not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad for ONLY
a
- index (a) index (b) is good for searches on ONLY b or ONLY a, and is
suboptimal for searching for BOTH a,b (although, faster then no index, but the
query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two
queries, one for a and one for b. If you then get a list of unique id's
of both, would it be faster to create an intersection yourself rather
than have the server do the legwork?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
On 2011/10/11 02:30 PM, Alex Schaft wrote:
On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no
difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and
'b' in the
second one. Looking for anything that matches 'b' for an index (a,b)
requires
a full scan as you don't know 'a', likewise searching for 'a' in an
index
(b,a) requires a full scan. See it as looking through a phonebook
trying to
locate someone by first- rather then lastname. It's in there, just
not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b)
then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad
for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad
for ONLY
a
- index (a) index (b) is good for searches on ONLY b or ONLY a,
and is
suboptimal for searching for BOTH a,b (although, faster then no
index, but the
query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two
queries, one for a and one for b. If you then get a list of unique
id's of both, would it be faster to create an intersection yourself
rather than have the server do the legwork?
Then there's index merge optimizations too I suppose
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Next question. If you have the two separate indexes and then do two queries, one for a and one for b. If you then get a list of unique id's of both, would it be faster to create an intersection yourself rather than have the server do the legwork? If you only have 2 unrelated indexes on a b, it depends on the data, the distribution of values, etc. No single answer here, test with your data and you'll have the results. If you need it often, I'd go for the combined index let MySQL do the work, which is probably fastest. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) ? Or is the decision based on the query response time ? On 11 Oct 2011, at 13:40, Rik Wasmus r...@grib.nl wrote: Next question. If you have the two separate indexes and then do two queries, one for a and one for b. If you then get a list of unique id's of both, would it be faster to create an intersection yourself rather than have the server do the legwork? If you only have 2 unrelated indexes on a b, it depends on the data, the distribution of values, etc. No single answer here, test with your data and you'll have the results. If you need it often, I'd go for the combined index let MySQL do the work, which is probably fastest. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) ? Or is the decision based on the query response time ? Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
- Original Message - From: Rik Wasmus r...@grib.nl Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. I'm trying to think of a scenario where index(a) would be beneficial in the presence of index(a,b). If both are available, and all else being equal, it's likely that the parser will pick the simplest index; but I can't see it having a major impact. Any full prefix of a combined index may be used; so afaik a separate index on any full prefix is a waste of diskspace and cycles. The net conclusion, Neil, is that you actually have to know what you're doing :-) Take the time to read the online documentation on mysql.com, it's pretty good. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Rik Wasmus r...@grib.nl Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. I'm trying to think of a scenario where index(a) would be beneficial in the presence of index(a,b). If both are available, and all else being equal, it's likely that the parser will pick the simplest index; but I can't see it having a major impact. Any full prefix of a combined index may be used; so afaik a separate index on any full prefix is a waste of diskspace and cycles. The net conclusion, Neil, is that you actually have to know what you're doing :-) Take the time to read the online documentation on mysql.com, it's pretty good. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: index question
John Mancuso wrote: If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475 Use explain, and find out. MySQL may reverse the two and join A to B inorder to use A's index. Since there is no WHERE clause, only one of the tables needs an index. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Run explain select * from A,B where A.col1=B.col1; The explain plan for your query will tell you what indexes are chosen. If your explain plan says what you do not like, definitely add an index on col1 in B. Make sure you run OPTIMIZE TABLE on both tables. The, run explain select * from A,B where A.col1=B.col1; again See what the explain plan says afterwards. Give it a try !!! - Original Message - From: John Mancuso [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 21, 2007 10:07:39 AM (GMT-0500) America/New_York Subject: index question If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index Question in MyISAM
Dan Salzer [EMAIL PROTECTED] wrote on 16/05/2005 14:36:41:
I have the following table:
CREATE TABLE `Article_Search` (
`ArticleID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`ArticleID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This table has several million rows, but I only want to search a subset
of
the table. IE:
SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('rubber
duckies' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018,
1452,
91)
The reason I'm specifying a set of ArticleIDs is that I know any hits
are
going to be within those articles. So the presence of the IN() clause is
purely there for performance. However, an explain on this Statement
shows
that it is using the Full-Text index. Is mysql text-searching the entire
table under the hood, or does it use the PK to reduce the dataset before
the
text-search.
MySQL can only use one index at a time. So if it used the ArticleID index
and your IN clkause as the primary index, it would be reduced to doing the
MATCH() the hard way, line by line, in the articles returned by the IN
clause.
On the other hand, you know that the only articles which contain the words
that you specify, it will be doiing a relatively fast lookup in the
FULLTEXT index to get the same set of IDs that you are feeding it, or an
even smaller one (because some even of those will not contained in the
hits). the only case where the simply doing the FUULTEXT search would not
be as fast as you quote would be when one of the separate words rubber
or duckies has a very large number of hits but the phrase does not.
In sum, I wouldn't bother with this optimisation unless your search truens
out in practice to be slow.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question part 2
rmck [EMAIL PROTECTED] wrote: I understand that I need to update the db's cardinality for this table I need speed Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one because with 56179085 records this could take a while... myisamchk -a does the same as ANALYZE TABLE. You can use either of them. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
- Original Message - From: rmck [EMAIL PROTECTED] Are my indexes all gone?? If so how do I recover them! Thanks hehe ;-) MySQL just doesn't know the cardinality of the indexes yet (the cardinality number is a *guess*) Try CHECK TABLE and I think you will see the cardinality numbers appear again. Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
- Original Message - From: rmck [EMAIL PROTECTED] Are my indexes all gone?? If so how do I recover them! Thanks hehe ;-) MySQL just doesn't know the cardinality of the indexes yet (the cardinality number is a *guess*) Try CHECK TABLE and I think you will see the cardinality numbers appear again. Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Did you run an ANALYZE TABLE? Original Message On 2/4/04, 9:33:30 AM, rmck [EMAIL PROTECTED] wrote regarding index question: I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+ -+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ -+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+ -+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+ -+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ -+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+ -+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- 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]
Re: Index Question
John Berman [EMAIL PROTECTED] wrote: Hi. using MYSql 3.28 There is no such version of MySQL :) I have a surname column with a standard index and this is the column were search are performed on, currently the filed only has the one name i.e.: Surname: smith I want to include other column data in the search i.e. fathersname, so I create an index on that column and in my search I use a statement like: surname = ' globsurname ' or fathersname = ' globsurname ' this does work but tends to slow the thing down, Because MySQL doesn't optimize search on two different keys with OR: http://www.mysql.com/doc/en/Searching_on_two_keys.html So Can I take the name from fathersname and include it in the Surname field so I have say: Surname: smith jones It does not seem to work for me, maybe I need a particular separator ? I want to go this way as each record my have several names that I want to be able to search on. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Lists - Jump [EMAIL PROTECTED] wrote: Ok, don't shoot me for not entirely understanding indexes. Can you build an index across two different tables w/in the same database? I need an index on fields in table a and in table b and I want that index to exist in table a. Is it possible? I'm running 4.0.12. Nope. You can't. Why do you need such index? Create the separate indexes. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Hi, You need to index the column that is used in the where clause try this ALTER TABLE tempo_resposta ADD INDEX idmaquina (idmaquina); HTH Dobromir Velev - Original Message - From: Leonardo Rodrigues Magalhães [EMAIL PROTECTED] To: MySQL ML [EMAIL PROTECTED] Sent: Friday, June 13, 2003 17:21 Subject: index question Hello Guys, I have the following table: CREATE TABLE tempo_resposta ( id int(11) NOT NULL auto_increment, idmaquina int(11) NOT NULL default '0', tempo int(11) NOT NULL default '0', horario datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; In this table, I'll be running this query: select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=SOMEID order by diferenca desc Right now, seems table is being completly scanned with this query: mysql explain select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=23 order by diferenca desc; ++--+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ -+ | tempo_resposta | ALL | NULL | NULL |NULL | NULL | 9216 | Using where; Using filesort | ++--+---+--+-+--+--+ -+ 1 row in set (0.00 sec) rows=9216, exactly all rows in the table Question: is there a way of creating an index for helping that kind of query ? I've tried creating index on horario, but it doesnt helped. Sincerily, Leonardo Rodrigues -- 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]
Re: index question
Something else to consider here: MySQL can use indexes to optimize order by as well, but your order by value can't be indexed since it is derived from a database lookup/calculation. If you really need to order by diferenca you may have to live with it, although you may still see Using filesort(which would indicate a slower query) in your explain since MySQL may have to make a second pass to sort the order by. Check out: http://www.mysql.com/doc/en/ORDER_BY_optimisation.html http://www.mysql.com/doc/en/MySQL_indexes.html Dobromir Velev [EMAIL PROTECTED] 06/13/03 10:03AM Hi, You need to index the column that is used in the where clause try this ALTER TABLE tempo_resposta ADD INDEX idmaquina (idmaquina); HTH Dobromir Velev - Original Message - From: Leonardo Rodrigues Magalhães [EMAIL PROTECTED] To: MySQL ML [EMAIL PROTECTED] Sent: Friday, June 13, 2003 17:21 Subject: index question Hello Guys, I have the following table: CREATE TABLE tempo_resposta ( id int(11) NOT NULL auto_increment, idmaquina int(11) NOT NULL default '0', tempo int(11) NOT NULL default '0', horario datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; In this table, I'll be running this query: select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=SOMEID order by diferenca desc Right now, seems table is being completly scanned with this query: mysql explain select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=23 order by diferenca desc; ++--+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ -+ | tempo_resposta | ALL | NULL | NULL |NULL | NULL | 9216 | Using where; Using filesort | ++--+---+--+-+--+--+ -+ 1 row in set (0.00 sec) rows=9216, exactly all rows in the table Question: is there a way of creating an index for helping that kind of query ? I've tried creating index on horario, but it doesnt helped. Sincerily, Leonardo Rodrigues -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
At 1:19 -0600 11/6/02, D. Walton wrote: At 01:05 AM 11/6/2002 -0600, you wrote: Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. From Jeremy's post, no such thing appears. He answered your question correctly. You were just asking the wrong question. :-) Use a single three-column index. Drop the two-column one and create a new one with the third column added. Paul, I'm not sure I understand. Are you agreeing with Jeremy or are you saying there is a solution, but I didn't ask my original question correctly? I'm afraid I don't see how using a single three-column index solves this? If I use a single three-column unique index then I can potentially add two rows with the same 'id'/'date' pairs so long as they both have a different 'value'. I need for no two records to have the same 'id'/'date' yet I also need the 'value' in the index for maximum lookup speed. Okay, I didn't understand that last part properly. Sorry. If you want to enforce uniqueness at the id/date level, you will need a separate unique index on just those two columns, in addition to a three column index on id/date/value. There is no syntax for specifying that you want an index on a set of columns but to enforce a uniqueness constraint on just a subset of those columns. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) If it creates a totally separate index then it will just have to update two indexes for every insert which in the end will slow things down. Yes. It's a design tradeoff you need to consider. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 91 days, processed 1,905,923,218 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) Seriously, it seems like there should be an optimizer in there that could pick out the fact that the second index is simply a subset of the primary key. It's very simple logic, even if this situation very seldomly occurs. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 20:39 -0600 11/5/02, D. Walton wrote: At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) In that case, what's necessary is for you to realize that you need do nothing. :-) If you have an index on id, date, and value, then id, date is a leftmost prefix of that index, and MySQL will happily use it. You need not create an explicit index on id, date. Nor on just id. Seriously, it seems like there should be an optimizer in there that could pick out the fact that the second index is simply a subset of the primary key. It's very simple logic, even if this situation very seldomly occurs. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 10:32 PM 11/5/2002 -0600, you wrote: At 20:39 -0600 11/5/02, D. Walton wrote: At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) In that case, what's necessary is for you to realize that you need do nothing. :-) If you have an index on id, date, and value, then id, date is a leftmost prefix of that index, and MySQL will happily use it. You need not create an explicit index on id, date. Nor on just id. Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 23:31 -0600 11/5/02, D. Walton wrote: At 10:32 PM 11/5/2002 -0600, you wrote: At 20:39 -0600 11/5/02, D. Walton wrote: At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) In that case, what's necessary is for you to realize that you need do nothing. :-) If you have an index on id, date, and value, then id, date is a leftmost prefix of that index, and MySQL will happily use it. You need not create an explicit index on id, date. Nor on just id. Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. From Jeremy's post, no such thing appears. He answered your question correctly. You were just asking the wrong question. :-) Use a single three-column index. Drop the two-column one and create a new one with the third column added. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 01:05 AM 11/6/2002 -0600, you wrote: Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. From Jeremy's post, no such thing appears. He answered your question correctly. You were just asking the wrong question. :-) Use a single three-column index. Drop the two-column one and create a new one with the third column added. Paul, I'm not sure I understand. Are you agreeing with Jeremy or are you saying there is a solution, but I didn't ask my original question correctly? I'm afraid I don't see how using a single three-column index solves this? If I use a single three-column unique index then I can potentially add two rows with the same 'id'/'date' pairs so long as they both have a different 'value'. I need for no two records to have the same 'id'/'date' yet I also need the 'value' in the index for maximum lookup speed. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index Question
From: [EMAIL PROTECTED] Can/should I index a timestamp column? Can: Yes. Should: Why not? Should I treat it as a char and limit it to just index on the portion of the data that distinguishes the date Hmm, I wouldn't. Offhand, I'd guess that MySQL handles time/date things as integers. I'm pretty sure that he also handles indexes as some sort of integer based serialization. Meaning that conversion to a string would be an extra step and thereby a loss. I'd say to try it a few different ways (if you have the time on this job) and find out what works best for you. --- Rodney Broom Programmer: Desert.Net Spam filter: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Index Question(again).
Hi, For such small tables, does it matter. MySQL will optimise queries the best way it thinks it can. In this case, in the first query, only the index file for student_info3 will be used, so that might have a bearing. In the second query, where all columns from the student table are requested with no restriction on the student_no column, a full table scan is seen as the best way of getting the data. Check the 'How MySQL uses Indexes' section of the manual. Hope this helps Quentin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 20 February 2001 07:30 To: MySQL Maillin List Subject: Index Question(again). No Body answered my previous mail. plz help me. I have three tables. i) student_info3 i) grade_ex3 i) test_info and, There are those Index In student_info3 table : index(student_no) In grade_ex3 table : index(student_no, test_no) In test_info table : index(test_no) When I use this SELECT statment, MySQL use indexes well. mysql explain select a.student_no from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+- -+-+ | a | index | student_no| student_no | 4 | NULL | 10 | Using index | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no | 1 | Using index | +---++---+--+-+---+- -+-+ 3 rows in set (0.00 sec) But When I use this kind of SELECT statement, Table a does not use index anyway. mysql explain select a.* from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+- -+-+ | a | ALL| student_no| NULL |NULL | NULL | 10 | | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no | 1 | Using index | +---++---+--+-+---+- -+-+ 3 rows in set (0.00 sec) Somebody help me! here are other information. mysql explain select b.korean from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---++-+---+-- +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++---++-+---+-- +-+ | a | index | student_no| student_no | 4 | NULL | 10 | Using index | | b | ALL| student_no_2 | NULL |NULL | NULL | 12 | where used | | c | eq_ref | PRIMARY | PRIMARY| 4 | b.test_no |1 | Using index | +---++---++-+---+-- +-+ this uses index well. mysql explain select c.test_name from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+- -+-+ | a | index | student_no| student_no | 4 | NULL | 10 | Using index | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no | 1 | | +---++---+--+-+---+- -+-+ 3 rows in set (0.00 sec) --- Member of N.N.R(New Network Research) Visit NNR.OR.KR --- ¿¥ÆĽº°¡ ¸¸µç ÆÈÆÈÇÑ ¸ÞÀÏ, ¿¥ÆÈ (http://www.empal.com) ¹®ÀåÀ¸·Î ã´Â °Ë»ö¿£Áø, ¿¥ÆĽº (http://www.empas.com) ½Å³ª´Â »ýÈ°¹®ÈÁ¤º¸, ½ÃƼ½ºÄÉÀÌÇÁ (http://www.cityscape.co.kr) The information
