Hey, I am trying to create a system to match wildcard domain names. Although the query above will work for me is it possible to index it at all? I don't have access to the DB to do any clever stuff as I am using Heroku to host my app but I can add indexes easy enough? Thanks! Edit: I would like to match wildcard domain names in my da...
I have a task to optimize this query, any idea will be appreciated. SELECT DISTINCT `br`.`id` AS `branch_id` , `br`.`store_id` , `br`.`longitude` , `br`.`latitude` , `br`.`town_id` , `br`.`post_region` , `cb`.`cat0_id` , `cb`.`cat1_id` , `cb`.`cat2_id` , `plan`.`listing_pla...
Hello everybody! We have a Java (Spring) web application with Tomcat servlet container. We have a something like blog. But the blog must load its posts dynamically with Ajax. The client's ajax script checks for new posts every second. I.e. Ajax must ask the server for new posts every second and it will be very heavy for database. But...
Hi Guys, I was wondering if anybody would have some input on how I could possibly optimize this MySQL query. I think I'm doing the right thing with indexes so don't think I can get this query any faster (it's currently taking more than 3 seconds to run) but would really love somebody with more experience to prove me wrong. Here's the q...
Hello I need a Windows based MySQL profiler. I've read the article where says that profiling is limited on Windows, but Im convinced that there has to be a tool which allows me at least to check all queries issued to the database, hopefully providing some more data. Can anyone help? ...
I have 2 tables (name(fields)): data(object_id, property_id, value_id) and string(id, value) All the data is in "string" table. "data" only refers to corresponding strings. For example, I have: data(1,2,3) data(1,4,5) data(6,4,7) string(1, 'car') string(2, 'color') string(3, 'red') string(4, 'make') string(5, 'audi') string(6, '...
I'm trying to filter a relationship table down to get a subset of the table where two conditions are met (ie: I want all of the id's of the entries who's color_ids are 1 or 2). It's a beefy table, so I'm trying to optimize as much as possible. I was wondering if anyone could explain my finding in this case: Why is SELECT DISTINCT a.id...
Hi! I'm looking for a way to optimize one SQL query that I have. I'm trying to get how many poems with a certain genre. Query looks like this: SELECT COUNT(*) FROM `poems` WHERE `id` IN ( SELECT `poem_id` FROM `poems_genres` WHERE `genre_title` = 'derision' ...
This is more of a design question. I have several places in models were I have records that have only a certain amount of option for them, for example: Sex: There is only 2 options (male, female) Level: There is only 3 options (silver, gold, platinum) Country: There is only a limited amount of countries. My question is what is the righ...
Hi, I'm trying to understand if it's right to use one table for votes for more than one argument: table_votes id | vote (±1) | id_user | timestamp | argument_type | argument_id table_photos // could be argument_type = 1 id | photo_file | photo_name | etc. table_house // could be argument_type = 2 id | house_name | etc. My prob...
I have written a fairly complex SQL query to get some statistics about animals from an animal sampling database. This query includes a number of subqueries and I would now like to see if it is possible to rewrite this query in any way to use joins instead of subqueries. I have a dim idea that this might reduce query time. (it's now about...
Hi, I manage a rather large traffic website. On each page we keep track of a visitors online by running the following code. We then have a cron which deletes the records which are older than X minutes. if(!isset($_SESSION[users_online_id])) { if(!(date('H')=='02' && date("i") >= 37 && date("i") <= 45)) { # NEW VISITOR - DOESN'T HAVE...
Hi, I'm trying to write a query to get a courses information and the number of bookings and attendees. Each course can have many bookings and each booking can have many attendees. We already have a working report, but it uses multiple queries to get the required information. One to get the courses, one to get the bookings, and one to g...
Hi, I have these table structures and while it works, using EXPLAIN on certain SQL queries gives 'Using temporary; Using filesort' on one of the table. This might hamper performance once the table is populated with thousands of data. Below are the table structure and explanations of the system. CREATE TABLE IF NOT EXISTS `jobapp` ( `i...
Hey guys, forgive me if this is too simple a question. I basically want a count of the number of males and females in a database. So I know two simple queries will accomplish this, such as: select count(*) from table where gender='male' select count(*) from table where gender='female' However, this seems very inefficient since I know ...
I have a SQL Server table with the following structure: CREATE TABLE [dbo].[Log]( [LogID] [bigint] IDENTITY(1,1) NOT NULL, [A] [int] NOT NULL, [B] [int] NOT NULL, [C] [int] NOT NULL, [D] [int] NOT NULL, [E] [int] NOT NULL, [Flag1] [bit] NOT NULL, [Flag2] [bit] NOT NULL, [Flag3] [bit] NOT NULL, [Counter] [int] NOT NULL, [Start...