Index, Then Query On Only Part Of The Index
Lets say we have a fulltext index on...
column 1, column 2, and column 3
If we do a select statement matching only on column 2 and column 3 will the index still be used effectively?
Do we then need a second index only on column 2, and column 3?
Would it be smart to simply create indexes on all of the following?
column 1
column 2
column 3
column 1, 2, 3
column 2, 3
?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Error : "cannot Be Part Of FULLTEXT Index"
in order to make it possible to do a fulltext search on multiple columns I created a temporary table which holds all the fields I want to search in from different tables. I'm creating a temporary table, create an fullindex on it, search in it, drop it. On our local server this works fine. But when I perform the code on the server of our client I get the following error message: "Column 'manufacturertitle' cannot be part of FULLTEXT index" This is caused by: "CREATE FULLTEXT INDEX full_index ON SearchTemp(producttitle, description, description2, manufacturertitle );" In the original table the field 'manufacturertitle' has the following properties: Type: varchar(255) Collation : utf8_general_ci Null : Yes I found the following solution somewhere but I have no to clue what to do now: "As of MySQL 4.1.1, full-text searches can be used with most multi-byte character sets. The exception is that for Unicode, the utf8 character set can be used, but not the ucs2 character set.".
Howto Add Another Index To A Default Index
I have a index rang of 1-5 set as default. I am now trying to add an index 6, i know it sounds easier enough but i cannot come right. Let me past the code segment below to get a better idea of what is happening: Code:
Performance Of UNIQUE Index Compared To "normal" INDEX
I have a table with several columns. one column "myColumn" of this table has a UNIQUE index on it. i want to use this column in many many search queries. does it make sense - in terms of performance - to add an additional INDEX to "myColumn"? or does an UNIQUE index already "include" a "normal" INDEX?
Query About INDEX
I am using a Servlet to access mysql db. I have a table called indexes(index_id,b_isbn,index_token). index_token contains token words which I will be searching. I created an INDEX(index_token, b_isbn) to speed up the search as there are thousands of entries in index_token. it all works fine, however I wish to speed up the search even more so I have created an algorithm that I will implement in the servlet. Im concerned that this might slow it down instead of speed it up.
Index This Query
Can someone shed some light on something I am not too familiar with... I have a table with 8 columns that I need to query as such SELECT col_1 FROM table1 WHERE col1=234 AND col2="sd" AND col3=1 how would I index this properly?
Index Checking From SQL Query
Is there a SQL query to check to see if an index (by name) exists on a table? I have this for SQL Server and for Oracle, and need to be able to query to see if an index exists before I try to create it. In some cases, I just need to be able to programmatically check to see if the index, by name, is there.
1-query-1-index Issue
Do you know how to come over the problem of 1-query-1-index in mysql?? (I'm working a lot with fulltext indexes)
Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed. The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
No Index Defined Error When Query
Will anyone tell me what's wrong with the following select statement: select * from email where (MdnName like '%".$lastName."%') This query does not return anything although there are records in the database that matches the criteria. I'm querying the contents from a php page with this code: $query = "select * from email where (MdnName like '%".$lastName."%')"; $result = mysql_query($query); $num_results = mysql_num_rows($result); When I tried runing the same select statement in PHPMyAdmin, I received this error: No Index Define!
Figuring Out When A Query Uses An Index On Join
The variable "Select_full_join" is showing a very high number when the recommendation is to have this at zero. I can get a full list of all queries generated on the site but how do I figure out which ones aren't using an index in the join?
Select Part Of The Value From A Query
I have a column in the table(Collection) called Contact where the data is in the format Name Ext eg. John lennon 1601. I want to write a query that will display just the name and another query that will display just the Ext--that is the right 4 numbers. Any clues what the 2 queries will be?
How To Do A Substr As Part Of A Query
I want to select from a table all records where the name field starts with a particular letter such as "a" or "b" and so on... but i cant seem to get the where statement to co-operate with me. this is being done in php (if that matters) and on a linux server. the current part of the query that i am trying (or should i say the last one i tried) is" AND substr(b_name,0,1) = '" . $alpha . "' AND ";
What Should I Index In This?
If this is my query and I'm calculating distance between users what should I index in the database? This query works really fast in my database of 10,000 users when I have state='xx' in the where clause. But if I don't it goes crazy and takes minutes! I'd love to sort by distance but that takes too long as well. Any ideas on what to index? SELECT SQL_CALC_FOUND_ROWS users_info.username,users_info.id ,CONCAT(zipcodes.city,', ', zipcodes.state) AS user_area ,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthdate)), '%Y')+0 AS user_age ,user_greeting.greeting , (3958 * 3.1415926 * sqrt((zipcodes.latitude - -111.978898) * (zipcodes.latitude - -111.978898) + cos(zipcodes.latitude / 57.29578) * cos(-111.978898 / 57.29578) * (zipcodes.longitude - +40.606125) * (zipcodes.longitude - +40.606125)) / 180) as distance FROM users_info LEFT JOIN user_greeting ON users_info.id = user_greeting.user_id INNER JOIN zipcodes ON users_info.zip = zipcodes.zipcode WHERE users_info.birthdate<��-02-16 06:34:03' AND users_info.birthdate>��-02-18 06:34:04' AND users_info.state='CA' AND users_info.sex = Ƈ' HAVING distance<ཕ' LIMIT 0,10
Should I Use Index?
I have table Files(id,name,type) . Type is INT, the possible values are(1,2,3,4,5). Often i execute query which gives me files of given type e.g.: SELECT id,name FROM files where type = 1
Why LIKE Not Using INDEX?
I have a table with an intger column called ID. I have an index on it. Now I want to get a result with all the rows whos ID values start with lets say 12....i.e. I want all the IDs with data - 12 123 1234 ........... select * from tablename where id like '123%' It is returning me correct results...but the query does not seem to be using the INDEX? My question is ... does MySQL not uses INDEX when we execute a LIKE command on a NUMERIC data.... __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw
Index
I read that it's preferable to shorten the length of an index if it's based on a varchar. But if I shorten a column defined as: name varchar(40) with an index of: index ix_name (name(10)) and then I try to do an insert into the table a "name" longer than 10 (but less than 40) the insert will fail with the error message "data too long for column name in row XXX". what am i doing wrong? (mysql 5.1)
Index
Not sure how indexes work with ORDER BY and GROUP BY. What index should I create which will be used on SELECT '' As ID, `Source Presidency` As Presidency, `Source Name` As Name, MIN(`Source Year`) As `Start Date`, MAX(`Source Year`) As `End Date` FROM burialdeathfull GROUP BY `Source Presidency`, `Source Name` ORDER BY `Source Presidency`, `Start Date` Everything I ttry doesn't work.
2 Index
i've been looking at some of my old databases with a view to optimising them as i'm totally self-taught and never realised loads of stuff like indexing when i created the databases! i'm using MySQL 3.23.58, all tables using MyISAM. on one of my tables (orders) i have built an index on the customer ID field but when i view it in phpMyAdmin this index has the same cardinality as the PRIMARY index (i.e. order number). now i know for sure that some customers have ordered more than once, so shouldn't the cardinality of the customer INDEX be lower than the PRIMARY INDEX? the customer ID field is type MEDIUMINT(8). on another database, in another table, i have 4 text columns for varying degrees of scientific calculations (species, genus etc). i have built a FULLTEXT index across the 4 columns as i want non-scientific people (who don't understand the difference between genus, species etc) to be able to query the database. now i don't seem to be able to add any other INDEXes on this table. i want to index the genus column by it's first letter so i can browse the table by letter. this works fine without the FULLTEXT INDEX but not with. is this right that a FULLTEXT INDEX can be the only INDEX (other than the PRIMARY) on a table.
Index To Hit First
I have a multi-million row table with three indexes in MySQL-5.0.15. These indexes have the following number of distinct values: date 415 block 100000 scan 45 If I'm doing a query on this table in what order should I hit these indexes? The largest first, to get rid of the most possibilities right away? Or the smallest first, because it has the fewest number of rows to scan to make the cut? At some level both seem to make sense, but I'm doing smallest to largest. Is this the most efficient? Or is this a case of just letting the query optimizer choose? I don't yet have a compound index on these fields.
Index Is Not Used :-(
It is a simple query and my index is not used :-( Index is "zadnj"e and I indexed "zadnjidatum, zadnjiuporabnik" together. Query is EXPLAIN SELECT r.ID, r.imerecepta, r.zadnjidatum, r.zadnjiuporabnik, r.stmnenj, o.ime, o.uporabnik FROM recepti r INNER JOIN obiskovalci o ON r.zadnjiuporabnik = o.ID ORDER BY r.zadnjidatum, r.zadnjiuporabnik Explain says: id select_type table type possible_keys key key_len ref rows Extra 1SIMPLErALLzadnjeNULLNULLNULL6788Using filesort 1SIMPLEoeq_refPRIMARYPRIMARY4r.zadnjiuporabnik1 What am I doing wrong? I used another similar query and I didn't get using filesort and not using index.
Using Index
I had a 1.6 Gb table (innodb) with 12 indexes, which I later reduced into a 250Mb table with 2 indexes. The problem is that the index seems to be bigger in size than the table (270Mb). What am I doing wrong? How can I make the index smaller?
Why Don't Use Index
I have a table Test with three fields: id int(11) not null auto_increment; name varchar(15) not null; age tinyint(4) not null. [1]select * from Test where id like '%1'; [2]select * from Test where id like '1%'; It's said that query[1] cannot use index, but query[2] can do. But with my tests, both of them didn't use index. My tests were very easy, I used "log_queries_not_using_indexes" option. The relevant statements in my.ini as follows, log-slow-queries=slow-query.log long_query_time=10 log_queries_not_using_indexes I just had a little data, so both queries weren't "long time query".
Index
what is index in mysql Action next to PK.
WHERE..IN Not Using Index
Here is the query: SELECT `vkey`, `data` FROM `registry` WHERE `vkey` IN('prioritycache','statuscache','staffcache','departmentcache','slaplancache','slaschedulecache', 'escalationrulecache','attachmenttypescache','ticketcountcache','ticketgridcache','ticketviewcache', 'queuecache','instaalertcache','ticketlabelcache','labelcountcache','gridcache','groupsettingcache', 'commentcache','staffassigncache','groupassigncache','tgroupassigncache','settingscache','tgroupcache','languagecache'); An INDEX exists on the `vkey` column. I've already OPTIMIZE, and ANALYZE this table. But when I run the query with EXPLAIN, no indexs are used. There are 58 rows in this table. If I reduce the number of options in the IN portion down to like, 5 or 6, the index is used. But if you increase past 5 or 6, the INDEX is no longer used.
INDEX Became KEY
i've created table manually and set INDEX on the one of my columns, then i exported my table with phpmyadmin and the INDEX became KEY in the output. are they similar?
Index
How to know if a column fileds are distinct or not ?
Index
I have a session table, where I stored user sessions in the database in a memory table. My question is, performance wise, would it be smarter to delete inactive sessions every 60 seconds via client http requests, or would it be wiser to select active sessions via a select that uses a where clause and searches a timestamp index column for active sessions, and then a cron job is scheduled to delete every 30 minutes. Which of these would be the better choice? On each page request I am going to have to select the number of sessions that exist and a second query that displays the active users. So if I go with my second choice, I will have to include a WHERE clause that scans the index on both those queries. But if I am issuing a delete every 60 seconds, then I won't have to do that, but then the delete is taking place every 60 seconds on a client request.
Getting The Index
I've got a table with an auto-incrementing index column (named "ID") which is also the primary key. After inserting a new record, is there an efficient way to get the index at which it was inserted? Presumably I could do "select max(id) from tablename", or "select ID from tablename order by ID desc limit 1". (Which one would be faster, BTW?) But that's assuming that no other records are inserted between the insert and the query. Is there a common method of doing this?
Index Help
I deleted a fulltext index on one of my mysql tables, which then broke my php frontend. I recreated the index, but the app is still broken. How can making a change to a secondary index, or in this case a fulltext index, adversly affect my application?
Index
Do I have set index (using KEY) on a field which is already indexed literly (using FULLTEXT INDEX)?
Index 7
The following is part of my script that does not work, the first line is what i think is causing the problem. going by the error returned. i have had this before and looked at my other script to work it out again, but can not see anything wrong $res = mysql_query("SELECT * FROM list WHERE email='$findstring' LIMIT 1") or die(mysql_error()); $name = mysql_result($res, 0, "name") or die(mysql_error());
An Index
I've a mysql database running with a lot of documentation files. Now I wonder how it is possible to generate an index file (in plain text) of this files sorted by description for publishing on my website.
Search Index
I have text stored in a database that I'd like to be searchable. However, searching through long strings and blobs seem like a very inefficient way of doing things. In MS Server there's an index server that will take all strings and create indexes out of words. This can significantly speed up text searches. Is there such a feature or tool for MySQL?
Delete And Re-index SQL
Out of interest -If I delete records from within the db are the records stored in a contiguous fashion or are there "gaps" in the table ? How do I go about deleting records from within a db and then RE-INDEX so that the records have new index id numbers?
No Index Defined!
I am working on editing of articles. Whatever the user enters to edit a particular article then that will be saved wow table(actual table), the already existing data goes to wow_history PHP $add_history = "INSERT INTO wow_history SELECT * FROM wow WHERE id = $id"; "; mysql_query($add_history); Before updating the table with new edits so i have wow and wow_history table identical except that in wow_history the id is not auto incremented (if auto incremented then ONLY THE ORIGINAL data copied stored in wow_history, but rest of the successive edits are lost) But now it says in wow_history that index not defined! So what should i do? Should i leave the wow_history without an index? The table i use is CREATE TABLE `wow_history` ( `id` int(11) unsigned default NULL, `contributed_by` text, `title` tinytext, `content` text, `date` bigint(20) NOT NULL default Ɔ', `mood` int(2) NOT NULL default Ɔ', `tags` text, `trusted` tinyint(1) NOT NULL default Ɔ', `modified` tinyint(1) NOT NULL default Ɔ', `ip` varchar(80) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I tried making uid (another field as autoincremented index but then data of wow is not copied to wow_history). Can i make timestamp as index? What are the drawbacks if there is no index?
Re-index Tables
How can I reset my tables so that the auto incremented primary key is reset from the beginning. I.e. I have been developing a db and have deleted/added updated data as I have gone along and my primary keys have holes in the index numbers. Is there a one shot command that resets all the values starting from one up to the current number of records in the table?
Index On A Primary Key?
I am trying to optimize a query that is running slow. The query uses a join: Quote: INNER JOIN table1 ON table1.field1 = table2.field2 AND table1.field3 = 'ok' However, when running EXPLAIN this returns id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table1 range PRIMARY,field3 field3 10 NULL 30015 Using where; Using temporary; Using filesort I have an index on table1.field3 already, however i cannot add an index on field1,field3 in table1 as field1 is a primary key (i understand indexing a primary key is bad practise)?! How do i get around this, as i understand mysql only uses 1 index per table per query?
Primary Key Or Index ?
I have a table contains 80.000 rows approx and I set (id) field to be primary key and index by using phpmyadmin. my question is that right ? or it must be primary key or index not both ....
It Doesnt Use My Index!
`player_1` mediumint(7) unsigned NOT NULL default Ɔ' `player_2` mediumint(7) unsigned NOT NULL default Ɔ' reffering to users' id-number in another table. I have two indexes: KEY `one` (`player_1`), KEY `two` (`player_2`) The cardinality of them is 6000 and 7000. My primary-key has cardinality 150 000. When i run this query: EXPLAIN SELECT * FROM `gamebase` WHERE player_1 =1 It uses the "one"-key (doh!). But now, that I run my next query (using OR), it tells me that one and two are possible keys, but it uses neither, but loops through my entire table "Using where". EXPLAIN SELECT * FROM `gamebase` WHERE player_1 =1 OR player_2 =1
Index Selectivity
I read that if your index selectivity is over 25% (thereabouts) mysql prefers a fulltable scan (it will be faster) I have the following table (simplified) id visibility (0, 1) adult (0, 1) I search only on these 2 queries: WHERE visibility = 1 (AND .. other parts of query not related to these 2 columns) WHERE visibility = 1 AND adult = 0 (AND ...) Should I have an index on both these columns? Visibility = 1 in 85% of the cases Adult = 0 in 50-80% of the cases
Index & Unique Key
I created a table with two columns 'user_id' and 'name', and defined the combination of the two columns as UNIQUE and defined an INDEX on each column. Why is the following error generated: UNIQUE and INDEX keys should not both be set for column `user_id`?
Index Question
I am seeing some weirdness on my DB. I was working on a project 3-4 months ago, and tweaked a query and indexes to work pretty fast (subsecond). Now I come back to it, and the query stalls...There are 6k more records in it than before (for a total of 13k). Here's a problem. My query is like this CODESELECT FROM t1,t2,t3 WHERE (...) AND t1.my_id NOT IN (SELECT my_id from t1 WHERE (...) )
Persistent Index?
I have a pretty sizeable table, nothing massive but big enough to warrant an index being created on what would be the "foreign key". The thing is that each day my program runs and it rebuilds the index and then drops it when it is completed, a process which takes about a minute, and I was wondering if this is being wasteful, and if in fact the index can just remain in place all the time? Will MySQL maintain the index as records are added/removed from the table? Or do I have to re-create the index before processing every day?
Index Usage
I have two tables, one is Student, which has studentid, personid, grade, fees as its columns. The other one is Person, which has personid, firstname, lastname, address as its columns. When given a studentid, I need to get the student' info, such as lastname from the db tables. I can do it with two queries as the following: 1. mysql> select personid FROM Student where studentid=1; 2. mysql> select lastname FROM Person where personid="the id I got above"; Or I can do it with one query: 3. mysql> select p.lastname from Student s, Person p where s.studentid=1 AND s.personid=p.personid Both works. However, I can't tell which one is actually faster, since MySQL shows me "1 row in set (0.00 sec)" for all these three queries. Both studentid and personid are primary keys, so they are automatically indexed. Will both indexes be used in query number 3? I saw people said that MySQL could use in a query only one index...Does it mean only one index will be used in query number 3? I am wondering which one will give better performance.
Primary And Index
Right now I have a member mysql database set up with a php login page. I am using MyPHPAdmin to do this. A member has to log in using their User_id number ( 4, 5 ,6 etc) I would like to make it so that they can log in using their email instead. 1-Do I edit the INDEX so that I make the primary the Email field? 2- If I do this does this mean I have to change anything in the PHP script? 3-Do I have to change anything else ,anywhere else to make this take effect?
Generated Index
We have a small database running on Win XP. We use Eclipse for development of our java app that uses this db. When I insert a new record into a table with one existing entry, the key that MySQL generates for it is a huge number and causes errors in subsequent processing. The table is created by this: CREATE TABLE scope ( scope_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, activitygroup_id INT NOT NULL UNIQUE, FOREIGN KEY (activitygroup_id) REFERENCES activitygroup(activitygroup_id) ON UPDATE CASCADE ON DELETE CASCADE ); We have several other similar tables, but this is the only one that causes me this problem. We tried using MySQL Query Browser to change the 'index kind' from INDEX to UNIQUE and that solved the problem for my co-worker. It didn't do anything for me, though, so I suspect it's a red herring. Can anyone suggest what I might try? I'm a completely new user. I started to look at the MySQL documentation and my eyes glazed over, so I'm quite ignorant of how this all works.
|
TRACKER
