makeee - Developer IT

Can I expect a performance gain from removing this JOIN?

- by makeee

I have a "items" table with 1 million rows and a "users" table with 20,000 rows. When I select from the "items" table I do a join on the "users" table (items.user_id = user.id), so that I can grab the "username" from the users table. I'm considering adding a username column to the items table and removing the join. Can I expect a decent performance increase from this? It's already quite fast, but it would be nice to decrease my load (which is pretty high). The downside is that if the user changes their username, items will still reflect their old username, but this is okay with me if I can expect a decent performance increase. I'm asking stackoverflow because benchmarks aren't telling me too much. Both queries finish very quickly. Regardless, I'm wondering if removing the join would lighten load on the database to any significant degree.

Read the article

Performance impact of rewrite conditions?

- by makeee

My web framework (cakephp) uses the following rewrite conditions and rule: RewriteCond %{REQUEST_FILENAME} !-d RewriteCond %{REQUEST_FILENAME} !-f RewriteRule ^(.*)$ index.php?url=$1 [QSA,L] I serve up a lot of image files (~10 a second). I'm wondering if it would improve performance to have a rewrite rule that exempted requests for files in my images directory from even trying those rewrite conditions (checking whether the file exists). My traffic is constantly fluctuating, so this would be hard to benchmark, which is why I thought I'd ask here. If that would be beneficial, how might I exclude files in "/images" directory from trying those conditions and rewrite rule?

Read the article

Good open source analytics/stats software in PHP?

- by makeee

The url shortening service I'm building needs to display some basic click stats to users: # of clicks, conversions, referring domains, and country (filterable by a date range). I'll possibly want more advanced stats in the future. Is there existing open source software that will allow me to pass events to it and then easily display a bar or line graph of that event (for example, a line graph of "conversions" between two specified dates). It seems like something like this should exist and would be much easier then building the whole thing from scratch. I know there are graphing scripts, but that still requires me to format the data (usually as an xml file) and then pass it to the graph. I'm looking for something a bit more complete, which I can just feed the events and then it does everything else.

Read the article

Need help optimizing MYSQL query with join

- by makeee

I'm doing a join between the "favorites" table (3 million rows) the "items" table (600k rows). The query is taking anywhere from .3 seconds to 2 seconds, and I'm hoping I can optimize it some. Favorites.faver_profile_id and Items.id are indexed. Instead of using the faver_profile_id index I created a new index on (faver_profile_id,id), which eliminated the filesort needed when sorting by id. Unfortunately this index doesn't help at all and I'll probably remove it (yay, 3 more hours of downtime to drop the index..) Any ideas on how I can optimize this query? In case it helps: Favorite.removed and Item.removed are "0" 98% of the time. Favorite.collection_id is NULL about 80% of the time. SELECT `Item`.`id`, `Item`.`source_image`, `Item`.`cached_image`, `Item`.`source_title`, `Item`.`source_url`, `Item`.`width`, `Item`.`height`, `Item`.`fave_count`, `Item`.`created` FROM `favorites` AS `Favorite` LEFT JOIN `items` AS `Item` ON (`Item`.`removed` = 0 AND `Favorite`.`notice_id` = `Item`.`id`) WHERE ((`faver_profile_id` = 1) AND (`collection_id` IS NULL) AND (`Favorite`.`removed` = 0) AND (`Item`.`removed` = '0')) ORDER BY `Favorite`.`id` desc LIMIT 50;

Read the article

Regular Expression: Allow letters, numbers, and spaces (with at least one letter or number)

- by makeee

I'm currently using this regex ^[A-Z0-9 _]*$ to accept letters, numbers, spaces and underscores. I need to modify it to require at least one number or letter somewhere in the string. Any help would be appreciated! This would be for validating usernames for my website. I'd actually like to support as many characters as I can, but just want to ensure that I prevent code injection and that characters will display fine for all users. So I'm definately open to regex validation suggestions that would support a wider set of characters.

Read the article

MYSQL sum() for distinct rows

- by makeee

I'm looking for help using sum() in my SQL query (not posting full query since the scenario is fairly simple). I have COUNT(DISTINCT conversions.id) in my query. I use DISTINCT because I'm doing "group by" for multiple columns and this ensures the same row is not counted more than once. Now I want to add: SUM(conversions.value) as conversion_value The problem is that the "value" for each row is counted more than once (due to the multiple group bys) I basically want to do SUM(conversions.value) for each DISTINCT conversions.id. Is that possible?

Read the article

Creating a file/folder structure and zipping it up?

- by makeee

I have a directory of image files and I need a php script or shell script that will rename them, create a structure of nested directories, and then insert each image into a specified place in the directory hierarchy. Ideally I would just specify a parent directory for each file and a parent directory for each directory and it would build it. And then finally, I need the script to zip up the whole thing. There's probably not an existing php class that will do all this for me, but if anyone knows of a php class or other script available online that would handle a lot of this logic that would be great.

Read the article

Detect mouse over iframe?

- by makeee

I'd like call a function every time a user drags their mouse over an iframe (which points to a different domain). Is this possible?

Read the article

Detect how long it takes for a file to upload (PHP)

- by makeee

Is it possible to know (serverside) the time it took for a file to upload? I have an image upload API and in my response I'd like to return the upload time (not including script execution time).

Developer IT