Speeding up queries with combined indexes


Does it speed up queries to use a combined index on the fields you're searching one, rather than indexes on each field

eg if we were to query with the condition: Code: SELECT * FROM my_table WHERE col1 < 100 AND col2>300 If we have an index on col1 and and index on col2, the optimiser would only use one of the indexes, would it not however if we added Code: alter my_table add index combined_index(col1,col2) The optimiser seems to prefer this joined index. I haven't run any benchmarks, but is this the way to go Utilising a combined index for a combination of fields you are likely to use in your condition Maybe there's a better term for it that I could look up.
Posted On: Sunday 21st of October 2012 10:05:00 PM Total Views:  148
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Use trigger to update ENUM field in another table

Is it possible to define a trigger on table A, which updates the enum values of table B based on the values in table A If I've got an enum field in my products table for 'productType' Code: alter table products add productType enum('BSK', 'CK','PDD') , and I want to store a more textual representation of these codes in a productTypes table Code: CREATE TABLE productTypes ( typeCode VARCHAR(10), typeText VARCHAR(200) ) INSERT INTO productTypes VALUES("BSK","Biscuits"), ("CK","Cakes"),("PDD","Puddings") If I want another productType value, I'd have to insert it into productTypes, and change the productType enum in products. Can I use a trigger to do this whenever the prodyctTypes table is changed
VIEWS ON THIS POST

98

Posted on:

Sunday 21st October 2012
View Replies!

MySQL Where Range and Grouping

To the gurus at SitePoint (incl. r937) : I have used MySQL for a very long time but haven't ever figured out a solution for this query problem that I have. Suppose I have a query as such: Code: SELECT column_x, FROM table WHERE column_a = and column_b >= GROUP BY column_c The index is on column_a, column_b, column_c. This unfortunately will yield a temporary table and a filesort. So I changed the code and added ORDER BY null; at the end of the clause. That gets rid of filesort but still uses a temporary table to get the results. Finally, I have re-created my index to the following: column_a, column_c, column_b. This doesn't use a filesort and temporary table, but I now believe MySQL is going into the table and not using the index to grab the results of column_b range. (Correct me if I'm wrong). My question - how can I create this query by using only indexes Is it possible I haven't found a solution yet but hoping someone can guide me.
VIEWS ON THIS POST

37

Posted on:

Sunday 21st October 2012
View Replies!

update with select count

I have a situation where I want to get a count of grouped records from one table and update a field with the count in another table. the select statement works fine as select count(t1.f1) as cnt, t1.f2, t1.f3 from reviews group by t1.f2, t1.f3 works great. Now I want to take cnt and update a field t2.f1 where t1.f2 = t2.f2 and t1.f3 = t2.f3 Every syntax I've tried I doesn't work and I can't seem to find anything in the manual. Can someone point me in the right direction.
VIEWS ON THIS POST

134

Posted on:

Sunday 21st October 2012
View Replies!

Need records where empid having duplaicates

10 sunil S/E 20 ravi T/E 30 naga S/E 40 john T/E 10 steven S/E 30 ravi T/E I need ouput as, 10 sunil S/E 10 steven S/E 30 naga S/E 30 ravi T/E
VIEWS ON THIS POST

115

Posted on:

Sunday 21st October 2012
View Replies!

List all duplicate values

Hi Guys, I have many duplicate email address in my table. My current sql get's me the total occurrence for each duplicate value, but I need to list all my duplicate email address so rather than grouping them I just want to list them. Any help appreciated. PHP Code: SELECT FirstName, LastName, Email, count(Email)ASOccurance, Company, Country, FROMdedupe GROUPBYEmail HAVING(COUNT(Email)>1);
VIEWS ON THIS POST

142

Posted on:

Sunday 21st October 2012
View Replies!

Struggling With Dynamic Grouping

I have a list of product SKUs in the following format: Brand Code (hyphen) Model Number (hyphen) Variant So for example: NIKE-SHOEA-WHITE etc. I have a query that *attempts* to get 5 of the most recent Model Numbers for a given brand. So here's some sample data: Code: sku date_added NIKE-SHOEA-WHITE 2009-08-23 NIKE-SHOEA-BLACK 2009-08-23 NIKE-SHOEA-GREEN 2010-09-23 NIKE-SHOEA-BLUE 2010-09-23 NIKE-SHOEB-WHITE 2010-09-23 NIKE-SHOEB-BLACK 2010-09-23 NIKE-SHOEB-RED 2010-09-23 NIKE-SHOEB-PURPLE 2010-09-23 Here's my current query (overly simplified): Code: SELECT p.sku AS product_sku FROM Products p WHERE p.active = 1 GROUP BY SUBSTRING_INDEX(p.sku, '-', 2) ORDER BY p.date_added DESC LIMIT 5 My expected result would be: NIKE-SHOEA-GREEN NIKE-SHOEB-WHITE My actual result is: NIKE-SHOEA-BLACK NIKE-SHOEB-BLACK (notice how NIKE-SHOEA-BLACK is actually over a year older than NIKE-SHOEA-BLUE and NIKE-SHOEA-GREEN) What appears to be happening is that the order by is sorting the results by date, and then the group by is applied. When the group by is applied (presumably for speed), each sub-group (meaning all NIKE-SHOEA) are then sorted by code (so since -BLACK comes first alphabetically, it is aways returned first regardless of the date_added field). So in technically if I say the query returns the "5 most recent models", the query is working, but what I am truly after is the "5 of the newest products, while showing no more than 1 of each model". I don't want to select a bunch of products and filter the results via the application if I don't have to. Can anyone think of a clever solution , Makes sense (as always). Tweaked the application and everything seems good. Looking back at it, what my application was displaying before was (in some cases) completely wrong
VIEWS ON THIS POST

212

Posted on:

Sunday 21st October 2012
View Replies!

Selecting Just One of Duplicates

I've an SQL select statement that I use to display some records within a DataList. Within my database there are duplicate records (not in error, they are supposed to be there), but instead of these duplicates showing in the Datalist for every record; I want just one to be shown. Records in database: ID Names 1 John 2 Sean 3 Michael 4 John 5 William Required selected: John Sean Michael William Does anyone know who to achieve this
VIEWS ON THIS POST

71

Posted on:

Sunday 21st October 2012
View Replies!

update query

I have two tables in which "id" is constant. Table 1 columns are: id, value_of The value_of column has the values "2010/07/10/name1_abc" Table2 columns are id, key,value_id value_id values are represented as "2010-07-11 00:00:00" Now I have to change the value in table1. Code: select value-of from table1 cv, table2 c where c.id=cv.id and key=28 and c.id=153 This will return the value 2010/07/15/lon-abc. Using the query I have check, if the values of "value_of" column and value_id are equal. If it is not equal then, I should not update "value_of". i.e "value_of" in table1 is "2010/07/10/name1_abc" and value_id is "2010-07-11 00:00:00", then value_of should be changed to "2010/07/11/name1_abc" if YYYY, MM, DD are same in two tables then I should not update the value or else I have to replace it.
VIEWS ON THIS POST

113

Posted on:

Sunday 21st October 2012
View Replies!

Updating table but duplicate issue keeps happening...

I have a table called "temp" (I was in the middle of teaching myself MySQL) and in this table, I have 3 columns: id, time, blah. The table had 4 rows. I removed number 2. I then tried my hand at UPDATE via the following: Code: UPDATE temp SET id=2; ...only to receive an error message: ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' What gives Obviously, I'm doing something wrong here but I had thought that since I removed the row, I could use update to bring the element back as using INSERT to do this received similar outcomes. None of my columns are set to AUTO INCREMENT and in my mind, I can't help but believe that I should be able to use either INSERT or UPDATE to create the element I deleted. What's up with this
VIEWS ON THIS POST

168

Posted on:

Sunday 21st October 2012
View Replies!

stop corruption - row replicate even after reboot

I've built a java application which updates my mysql database with data retrieved off the local network. the mysql server is on a remove vps. unfortunately there was a a little problem and the local pc running the program died with the windows blue screen (its a fairly old pc only used for running the program). anyway the real problem is after i has going through some of the data i found one record (last one inserted) was replicated about 1000+ times. I thought it odd and deleted all the replicated rows. a few minutes later another check found the same record still duplicating after a count last showed 0. i ran this query Code: SELECT ikey, COUNT(ikey) AS numberOfEntries FROM tablename GROUP BY ikey HAVING ( COUNT(ikey) > 1 ); it showed about 20 records when i first started writing this post and just ran it now its showing just over 100. Can anyone suggest anything to stop this please. I've tried rebooting the mysql server then rebooting the whole vps but it just continues... any ideas anyone
VIEWS ON THIS POST

104

Posted on:

Sunday 21st October 2012
View Replies!

Group By Multiple Generated Columns

Code MySQL: SELECT COUNT(*) AS Members, CASE INTERVAL( rankOrder, 14, 26, 38, 48 ) WHEN -1 THEN "NULL" WHEN 0 THEN "Flag" WHEN 1 THEN "SO" WHEN 2 THEN "JO" WHEN 3 THEN "NCO" WHEN 4 THEN "ENL" END AS RankCat, CASE INTERVAL( loa, 1, 3, 4) WHEN -1 THEN "NULL" WHEN 0 THEN "Active" WHEN 1 THEN "LoA" WHEN 2 THEN "AWOL" WHEN 3 THEN "FTR" END AS LoACat FROM sms_crew GROUP BY RankCat,LoACat; The code runs, but the results returned seem to only be grouped by LoACat; the RankCat grouping gets thrown away.
VIEWS ON THIS POST

63

Posted on:

Sunday 21st October 2012
View Replies!

Remove duplicate rows

there, I have a code to remove the duplicates in one of my table but I cant seem to be able to ORDER the way mysql select the values. Here is my initial table: Code: mysql_query("CREATE TABLE new_table as SELECT * FROM test GROUP BY url") mysql_query("DROP TABLE test") mysql_query("RENAME TABLE new_table TO test") I want to remove the duplicated URL and make sure that it keeps only the value with the higher PR. I tried using the ORDER BY attribute but it doesnt seem to work. The first 3 values with a PR of 0 are always selected and placed in the new table... The good result should be that the 3 values (a,b,c) with a PR of 6 are found in the final table. I tried this and it doesnt work: Code: mysql_query("CREATE TABLE new_table as SELECT * FROM test GROUP BY url ORDER BY pr DESC") I am looking for a fast way to achieve this because my table have over 200k entries.
VIEWS ON THIS POST

137

Posted on:

Sunday 21st October 2012
View Replies!

call update mysql procedure from foxpro 9

how to create update store procedure in mysql and call it from foxpro 9 best regard purba
VIEWS ON THIS POST

122

Posted on:

Sunday 21st October 2012
View Replies!

access denied you need the super privilege for this operation in mysql

access denied you need the super privilege for this operation in mysql
VIEWS ON THIS POST

127

Posted on:

Sunday 21st October 2012
View Replies!

Auto Backup

hai ., How to backup the mysql databaseusing asp.net code advance
VIEWS ON THIS POST

52

Posted on:

Sunday 21st October 2012
View Replies!

Group by all months

Hi Can anybody expand on this code to include zero's for the missing months http://www.sqlhacks.com/index.php/Dates/GroupByMonth If you have a look at the heading on the page (at the bottom) "Right way of grouping by month in SQL Server" It only returns a result list for months that have units sold. but how about including all months.. e.g. Code: Year Month Units sold ----------- ----------- --------------------------------------- 2004 1 0.00 2004 2 6.00 2004 3 5.00 After doing some googling i read that I need to join to another table containing a list of all the months But do I also need a table with all the years I am using MySQL
VIEWS ON THIS POST

53

Posted on:

Sunday 21st October 2012
View Replies!

Best Automated Backup Solution?

Im tryin to figure out the best way to Backup a MySQL and the FTP for a site if possible.. some solutions backup but to the FTP of the site and that doesnt seem to safe .. what options do I have I have about 20 sites I would like to automatically backup to another location in case there is a problem with the site so it can be easily restored.. any suggestions
VIEWS ON THIS POST

53

Posted on:

Sunday 21st October 2012
View Replies!

MySQL Python auto-increment foreign-key clean-up

Hi Everybody, I am working with Python MySQL, and need to clean a table in my database that has 13328 rows. I can not make a simple drop table, because this table is child and also father of other child foreign-keys linked on it. If I try drop table, the system forbidden me. The table is defined with ON UPDATE CASCADE, ON DELETE CASCADE and InnoDB; The primary_key index to that table is defined as Code: productID INT(6) NOT NULL AUTO_INCREMENT ... PRIMARY KEY (productID,productNO) Therefore, I just have to clean; this will automatically restore the table primary key index to 1 for the next input. Right This procedure worked fine for another table that was a father table, but not also a father and child table. But, for this table, which is child and father of other tables, I got stuck on it. Here is the code - productID is my primary index key to this table: Code: def clean_tableProduct(self): getMaxID_MySQLQuery = """SELECT MAX(productID) FROM product;""" cleanTabeMySQLQuery="""DELETE FROM product WHERE productID
VIEWS ON THIS POST

107

Posted on:

Sunday 21st October 2012
View Replies!

MS Access update multiple rows with one query question

I'm not sure that I can do this in SQL but if I can it might be more efficient than looping in code. I have a two tables that are related via a third table. The first table is for members and just has a bunch of personal info: name, address, email, etc... An ID column is the key. The second table is for professional services each individual offers and also has an ID column which is the key The third table has two columns... one for member IDs and the other for services IDs. Both columns are keys. I have an editing form (webpage) that lists out professional services with a checkbox for each. I know I can loop through the results of an update form in ASP VBScript or ASP.NET and update each one that has been selected but I wonder if there isn't a more efficient and cleaner method of doing it with SQL. I would need to: insert a record if it doesn't exist and has been submitted from the formremove a record if it does exist but hasn't been submitted And that's about it. Not difficult to do with code but I think a little primitive. Any ideas
VIEWS ON THIS POST

144

Posted on:

Sunday 21st October 2012
View Replies!

insert into table is doesn't exist and update if it does?

I read on one of the threads sitepoint search brought up that I can do it with something like the "ON DUPLICATE KEY" or so but in my table using unique key is not possible. table `members` has fields `member_name`, `age`, `gender`. example entry is: Code MySQL: INSERT INTO `members` (`member_name`, `age`, `gender`) VALUES ('Tanya', '18', 'female') However, I want to do the following if 'Tanya' already exists, Code MySQL: UPDATE `members` SET `age`='18', `gender`='female' WHERE `member_name`='Tanya' . I can check if it exists via php and do appropriate action, but is there a way to do this through mysql
VIEWS ON THIS POST

116

Posted on:

Sunday 21st October 2012
View Replies!