ajlisowski

From my experience if you have one index on each field, as opposed to having one on both, it will not use the index. I recently had an issue where I was not indexing on both fields (and instead had two indexes on each individual field) and my query was taking literally 90 seconds. I added the index on both fields and it cut it down to like 800ms.

yes, you could get all the data you needed with a join. SELECT f.`media_type`, i.`file` AS `image`, v.`file` AS ` `video`, s.`file` AS `sound` FROM `featured_media_table` AS `f` LEFT JOIN `image_table` AS `i` ON (f.`media_id`=i.`media_id` AND f.`media_type`=1) LEFT JOIN `video_table` AS `v` ON (f.`media_id`=v.`media_id` AND f.`media_type`=2) LEFT JOIN `sound_table` AS `s` ON (f.`media_id`=s.`media_id` AND f.`media_type`=3) however...i dont see a reason to have the video, image and sound files seperate like that. Do the tables contain other information that is unique to a file type? Like captions and stuff? if so, id set it up as follows: file_table (contains id, file location and type as well as any other info shared by all 3 types) image_table (contains id and any info unique to the image) video_table (contains id and any info unique to the video) sound_table (contains id and any info unique to the sound) that way when you are just grabbing basic shared info you wont need to mess with un-needed joins. If, however, video, image and sound tables dont have any unqiue fields, you might as well combine them to just have the one table.

SELECT id, mission, level, location, loot FROM missions WHERE id NOT IN (SELECT miss_id FROM comp)

This should probably be moved to PHP and not mysql since if you are validating through AD you wont need mysql. http://php.net/manual/en/book.ldap.php Basically ldap is a protocol which will allow you to query your active directory database. For example, you could prompt a user for username and password and then use ldap to validate this binding in AD. You could also grab all users in a certain group or with certain fields. http://www.php.net/manual/en/function.ldap-bind.php There are PHP classes available which already do this http://adldap.sourceforge.net/ I happened to write my own which is probably a lot worse, but I did so to get an understanding of what I was doing. Its been a few months since Ive messed with the code behind my authentication, but basically you connect to the AD server, then to validate a login you simply attempt to use the ldap-bind function to try and connect as that user. Im definately no expert, as I hadnt even heard of AD until earlier this year. My company actually wants a single sign on solution where we will automatically bind the user based on windows credentials (so the intranet wouldnt even prompt a login if it they were already on the domain) I have yet to accomplish that since most apache mods for it seem to be unsupported. But I have gotten the system to prompt for credentials and validate on AD. I would look at the class I linked to, and some tutorials on LDAP (jsut google php ldap) and using the tutorials the the php code in the class, learn how to access AD in php. Im no where near experienced enough to give you a good breakdown of what to do, but if you have any specific questions after looking at the class or some tutorials feel free to ask.

ah, gotcha... thats a bit trickier SELECT g.`Group_ID`, g.`Group_Name`, IF(g.`Parent_Group_ID` !=0,p.`Group_Name`,g.`Group_Name`) AS `parent_group` FROM `group` AS `g` LEFT JOIN `group` AS `p` ON g.`Parent_Group_ID`=p.`Group_ID` ORDER BY `parent_group`, g.`Parent_Group_ID`, g.`Group_Name` Basically, it should set the parent name equal to the current name if it doesnt have a parent. Then order by the parent name and the parent ID and the group_name. This, however, will likely not work if you have multiple layers of parents. For example Science-Biology-Genetics. It will be thrown off because it will put genetics ordered by Biology and not science. However, for a simple 1 layer of parent-child it should work.

SELECT t.`topic`,t.`author` AS `thread_author`, t.`postdate` AS `thread_postdate`, t.`message` AS `thread_message`, p.`author` AS `post_author`, p.`postdate` AS `post_postdate`, p.`message` AS `post_message` FROM `threads` as `t` LEFT JOIN `posts` AS `p` ON t.`id`=p.`thread_id` WHERE t.`id`=3 this will get a joined result of threads/posts with a thread ID of 3.

UPDATE `images` SET `position`=`position`+1 WHERE `position`>=2

SELECT `Group_ID, `Group_Name`, `Parent_Group_ID` FROM `group` ORDER BY `Group_Name` you would not need a subquery... However, if you wished to have the parent group name, instead of the ID, you could do this... SELECT g.`Group_ID, g.`Group_Name`, p.`Group_Name` AS `Parent_Group_Name` FROM `group` AS `g` LEFT JOIN `group` AS `p` ON g.`Parent_Group_ID`=p.`Group_ID` ORDER BY g.`Group_Name`

You likely wouldnt be able to use mysql to validate a user in AD. You could, however, use PHP to do so using the LDAP library. I have done so, basically requiring a log-on which validates an active directory binding which allows the user access to an intranet system. I do not use mysql for any of the authentication process (except I do set system admins in mysql to provide certain AD users access to the system's backend functionality) What exactly are you looking to accomplish? Are you using PHP?

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lunapr_c1price range style_name style_name 27 NULL 13607 Using where; Using index

I get an error..."Can't group on 'countof'" In case it helps, there are 7,935 distinct values of "style_name" and there are a total of 13,608 distinct rows in the table. if I run select style_name, count(*) as countof FROM lunapr_c1price GROUP BY style_name I get 7,935 results, most of which have a countof value of 1, some have more then that. One had 10...but a vast majority had 1.

I put an index on style_name, and it is a pretty high cardinality (6804, so about a 1:2) However it still does not seem to be using it. Putting an index that is both manufactuer and dist worked. In fact I went back and made that combination the primary key. I also attemepted to make a foreign key to those fields, but I am not sure if that helped. The query time is drastically lower with those two fields. I still can not figure out why style_name is not using its index though.

You would populate the link table when adding a new actor or movie. So if Chrstian Bale is in like 10 movies, when he is added you would need to add all 10 elements into the link table. To grab what you would need, your query would look similar to this... SELECT m.`film`, a.`name` FROM `title` AS `m` LEFT JOIN `film2talent` AS `l` ON m.`id`=l.`film` LEFT JOIN `talent` AS `a` ON l.`talent`=a.`id`

Hi everyone, Ive been tasked with building a spreadsheet type application that allows users to modify columns and change filters on the fly. Its a pretty intense database with lots of different tables so in order to access any of the datas columns, there has to be joins. It worked great when there were maybe 20 or so rows per table, but now theres 10k-30k in most of them, and needless to say its running much slower. However I figured I could index any column that the user can filter by, to speed things up. This does not seem to have helped.... (note, I know there are un-needed joins in the query right now. I am testing for a worst case scenerio where the user is requesting data from all available tables) SELECT p.`sku` AS `id` , p.`luna_sku` , hp.`edges` , f.`fiber` , p.`style_name` FROM `lunapr_c1price` AS `p` LEFT JOIN `lunapr_c1hard` AS `hp` ON p.`luna_sku` = hp.`luna_sku` LEFT JOIN `lunapr_c1soft` AS `sp` ON p.`luna_sku` = sp.`luna_sku` LEFT JOIN `lunapr_c1vendor` AS `v` ON ( p.`manufacturer` = v.`manufacturer` AND p.`division_dist` = v.`division_dist` ) LEFT JOIN `lunapr_c1geoprice` AS `pr` ON ( p.`luna_sku` = pr.`luna_sku` AND pr.`current` = '1' ) LEFT JOIN `lunapr_c1geocost` AS `c` ON ( p.`luna_sku` = c.`luna_sku` AND c.`current` = '1' ) LEFT JOIN `lunapr_c1fiber` AS `f` ON sp.`fiber` = f.`id` WHERE ( p.`style_name` != 'Alaska Sg Maple2.5 ' ) ORDER BY f.`fiber` DESC LIMIT 0 , 10 This query takes like 24 seconds to complete...thats no good. SELECT p.`sku` AS `id` , p.`luna_sku` , hp.`edges` , f.`fiber` , p.`style_name` FROM `lunapr_c1price` AS `p` LEFT JOIN `lunapr_c1hard` AS `hp` ON p.`luna_sku` = hp.`luna_sku` LEFT JOIN `lunapr_c1soft` AS `sp` ON p.`luna_sku` = sp.`luna_sku` LEFT JOIN `lunapr_c1vendor` AS `v` ON ( p.`manufacturer` = v.`manufacturer` AND p.`division_dist` = v.`division_dist` ) LEFT JOIN `lunapr_c1geoprice` AS `pr` ON ( p.`luna_sku` = pr.`luna_sku` AND pr.`current` = '1' ) LEFT JOIN `lunapr_c1geocost` AS `c` ON ( p.`luna_sku` = c.`luna_sku` AND c.`current` = '1' ) LEFT JOIN `lunapr_c1fiber` AS `f` ON sp.`fiber` = f.`id` WHERE ( p.`style_name` != ' ' ) ORDER BY f.`fiber` DESC LIMIT 0 , 10 This query takes like 1.4 seconds. Still not great but bareable. Any idea why they would be so drastically different? SELECT p.`sku` AS `id` , p.`luna_sku` , hp.`edges` , f.`fiber` , p.`style_name` FROM `lunapr_c1price` AS `p` LEFT JOIN `lunapr_c1hard` AS `hp` ON p.`luna_sku` = hp.`luna_sku` LEFT JOIN `lunapr_c1soft` AS `sp` ON p.`luna_sku` = sp.`luna_sku` LEFT JOIN `lunapr_c1fiber` AS `f` ON sp.`fiber` = f.`id` WHERE ( p.`style_name` != 'Alaska Sg Maple2.5 ' ) ORDER BY f.`fiber` DESC LIMIT 0 , 10 This one only takes .8 seconds. So obviously removing the other joins helps. (while writing this post I unset and reset some indexes and made things, worse, the big query now takes 140+seconds...) This is the explain on the nightmare query id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p range style_name style_name 27 NULL 13607 Using where; Using temporary; Using filesort 1 SIMPLE hp ref luna_sku luna_sku 9 luna_filepro.p.luna_sku 1 1 SIMPLE sp ref luna_sku luna_sku 9 luna_filepro.p.luna_sku 1 Using index 1 SIMPLE v ref division_dist,manufacturer division_dist 27 luna_filepro.p.division_dist 2 1 SIMPLE pr ref luna_sku luna_sku 9 luna_filepro.p.luna_sku 8 1 SIMPLE c ref luna_sku luna_sku 9 luna_filepro.p.luna_sku 2 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 luna_filepro.sp.fiber 1 I assume it is because its only using division_dist as a ref for `v` instead of the combination of division_dist and manufacturer. Any ideas? If remove that one join (the vendor join) it is decent, once I include that its a nightmare.